error with IF EXISTS ()

Question

IF NOT EXISTS (SELECT 1 FROM Products WHERE name='Iphone1' AND manufacturer='appl') THEN
      INSERT INTO Products(product_id, name, category, manufacturer) 
         VALUES (10000, 'IphoneZ', null, 'appl');

ERROR: syntax error at or near "IF" LINE 1: IF NOT EXISTS (SELECT 1 FROM Products WHERE name='Iphone1' A...

Can anyone help me understand what I am doing wrong?

read the manual http://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html — 2oppin, Dec 02 '16 at 10:27
Googling your title and 'mysql': http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists. Please read [ask]. — philipxy, Dec 02 '16 at 10:47

score 0 · Answer 1 · answered Dec 02 '16 at 10:23

MySQL only supports the IF statement in programming blocks -- stored procedures, functions, and triggers. Hence you cannot do what you want that way.

Instead, you can just write a single query:

INSERT INTO Products (product_id, name, category, manufacturer)
    SELECT product_id, name, category, manufacturer
    FROM (SELECT 10000 as product_id, 'IphoneZ' as name, null as category, 'appl' as manufacturer) t
    WHERE NOT EXISTS (SELECT 1 FROM Products p WHERE p.name = t.name and p.manufacturer = t.manufacturer);

Actually, though, it is best to have the database directly enforce this sort of uniqueness. You can do so with a unique constraint/index:

CREATE UNIQUE INDEX unq_product_manufacturer_name ON product(manufacturer, name);

Then you can write an query to ignore errors by doing:

INSERT INTO Products (product_id, name, category, manufacturer)
    VALUES (10000, 'IphoneZ', null, 'appl')
    ON DUPLICATE KEY UPDATE category = VALUES(category);

The ON DUPLICATE KEY doesn't do anything -- it just serves to avoid returning an error if a duplicate value is inserted.

score 0 · Answer 2 · answered Dec 02 '16 at 10:24

You are missing END IF keyword at the end of IF statement. And also, this SQL statement can be only used in a Routine block such as Stored Procedure or Stored Function.

Your SQL should be like this:

IF NOT EXISTS (SELECT 1 FROM Products WHERE name='Iphone1' AND manufacturer = 'appl') THEN
  INSERT INTO Products(product_id, name, category, manufacturer) 
  VALUES (10000, 'IphoneZ', null, 'appl');
END IF;

score 0 · Answer 3 · answered Dec 02 '16 at 10:54

Is it MySQL or SQL server?
I think you type wrongly : name='Iphone1' . It should be 'IphoneZ'.
You used single quotes in some places and double quotes in some places.

If it is mysql then try below query INSERT INTO Products(product_id, name, category, manufacturer) SELECT * FROM (select 10000,'IphoneZ', null, 'appl') AS tmp_table WHERE NOT EXISTS (SELECT 1 FROM Products_arun WHERE name='IphoneZ' AND manufacturer='appl') LIMIT 1;

If it is SQL server then try below query IF NOT EXISTS (SELECT 1 FROM product WHERE name='IphoneZ' AND manufacturer='appl') INSERT INTO product(product_id, name, category, manufacturer) VALUES (10000, 'IphoneZ', null, 'appl');

error with IF EXISTS ()

3 Answers3