mysql insert value if it doesn't exist

Question

I'm trying to insert an ingredient to an ingredients table if it doesn't exist. I'm using the following syntax:

INSERT INTO ingredient(Name) 
(
 SELECT 'ingName' FROM dummytable WHERE 
(SELECT count(*) FROM ingredient WHERE Name = 'ingName')=0)

This does not seem to work (0 rows affected), even though the SELECT query seem to return the desired result (an entry which contains "ingName").

The "ingredient" table has 2 columns: Name, id (id is auto incremented)

Thanks, Li

score 7 · Answer 1 · answered May 13 '12 at 19:20

7

Its better to add unique index on the name column:

ALTER TABLE `ingredient` ADD UNIQUE(`Name`)

After doing that you can use INSERT IGNORE:

INSERT IGNORE INTO `ingredient` ... /* anything */

answered May 13 '12 at 19:20

Vitaly Muminov

1,914
12
11

this is a better solution for the problem – nawfal May 13 '12 at 19:29
Thanks :) I will probably switch to this solution, but I was really curious to find out why my code didn't work... – user429400 May 13 '12 at 19:39

Rahul · Accepted Answer · 2012-05-13T19:23:59.140

That's because your inner query SELECT count(*) FROM ingredient WHERE Name = 'ingName' is returning a value > 0 and hence the upper query SELECT 'ingName' FROM dummytable WHERE is not selecting any rows and so no insert is happening.

I tried the same with a test table having 2 column name|value and it did worked fine

    INSERT INTO test1(name) (
    SELECT 'name' FROM test2 WHERE  
   (
      SELECT count(*) FROM test2 WHERE name = 'bilboa'
     )
   =0
   )

Inserted 2 rows with value name cause that's what I am selecting in select query and my test table 2 rows.

mysql insert value if it doesn't exist

2 Answers2