Geek asks and answers

Mysql insert value if it does not exist

I am trying to insert an ingredient in an ingredient table if it does not exist. I use the following syntax:

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

This does not work (0 rows are affected), although the SELECT query seems to return the desired result (a record containing "ingName").

The table "ingredient" has 2 columns: Name, identifier (identifier automatically increases)

Thanks Li

+3
source share
2 answers

This is because your internal query SELECT count(*) FROM ingredient WHERE Name = 'ingName'returns a value> 0, and therefore the top query SELECT 'ingName' FROM dummytable WHEREdoes not select any rows, and therefore no insertion occurs.

I tried the same thing with a test table having 2 columns name|valueand it worked great

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

2 name , select query 2 .

+3

name:

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

INSERT IGNORE:

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

More articles:


All Articles