How to insert in mysql if record does not exist?

Question

my db is

TABLE

        id | new_id  |  affiliate_id  | s_percent | a_percent |   date  
        ---+---------+----------------+-----------+-----------+--------------
        32 |  970    |     34         |     0.25  |   0.2     | 1404387120
        33 |  972    |     29         |     0.25  |   0.2     | 1404482095
        34 |  973    |     26         |     0.25  |   0.33    | 1405752775

and i want to insert new record here .If new_id already exists in its column then it do not insert

concept you'll need: `primary_key` it is not `db` it is your `table structure`. and please have a look at: http://stackoverflow.com/questions/913841/mysql-conditional-insert — Vedant Terkar, Jul 19 '14 at 08:21
if i understand correctly, you need unique key for `new_id` column, to ensure, there is unique value in `new_id`... therefore, insert will not insert the row, if the `new_id` value is already in the table — Miro Hudak, Jul 19 '14 at 08:24

score 0 · Accepted Answer · answered Jul 19 '14 at 08:26

That's not a good approach, however if you insist to do so, you can use below code:

INSERT INTO table_listnames (id,new_id,affiliate_id,s_percent,a_percent,date )
SELECT * FROM (SELECT 34,973,26,0.25,0.33,1405752775) AS tmp
WHERE NOT EXISTS (
    SELECT new_id FROM table_listnames WHERE new_id = '970'
) LIMIT 1;

It would be better to create a UNIQUE INDEX on the field which should be unique (new_id), then use either:

normal INSERT (and handle the error if the new_id already exists),
INSERT IGNORE (which will fail silently cause a warning (instead of error) if new_id already exists)
INSERT ... ON DUPLICATE KEY UPDATE

How to insert in mysql if record does not exist?

1 Answers1