0

I have a SQL syntax error with my IF NOT EXISTS on line 1 when I tried to do this request on MySQL, and I can't figure why.

IF NOT EXISTS (SELECT * FROM `my_table` WHERE first_name = 'Testfn')
BEGIN
INSERT INTO `my_table` (first_name)
VALUES ('Testfn')
END;

I'm trying to insert first_name only if there is no other same first name in my_table.

I also tried this syntax, but I still have the error 1064 "You have an error in your SQL syntax" :

IF NOT EXISTS (SELECT * FROM `my_table` WHERE first_name = 'Testfn') THEN
INSERT INTO `my_table` (first_name)
VALUES ('Testfn')
END IF;

I tried SELECT * FROMmy_tableWHERE first_name = 'Testfn' separately, and it works.

And like this doesn't work too :

INSERT INTO `my_table` (first_name)
VALUES ('Testfn')
WHERE NOT EXISTS (SELECT * FROM `my_table` WHERE first_name = 'Testfn');

EDIT : first_name is UNIQUE in the database.

Emilie
  • 668
  • 1
  • 9
  • 21
  • I think you can see from here http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – hungneox Apr 03 '14 at 12:10
  • please mention your actual table name,cause syntax seems to be correct.Also mention error that you are getting. – AK47 Apr 03 '14 at 12:10
  • 1
    http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table – Nagaraj S Apr 03 '14 at 12:11
  • Have you read the manual on the MySQL IF statement (http://dev.mysql.com/doc/refman/5.5/en/if.html)? The syntax is as follows: IF [condition] THEN [command] END IF; – Tim Burch Apr 03 '14 at 12:26
  • @TimBurch : I tested with this syntax, but still I have a syntax error. – Emilie Apr 03 '14 at 12:36
  • Will you please post the exact SQL you used and the error message you receive. – Tim Burch Apr 03 '14 at 12:43

1 Answers1

0

You have not need to write a column name, without specifying you can try to insert because we already checked condition on above. So basically you can do it using Merge Statement like.

MERGE INTO my_table
USING ( 
   SELECT first_name
) t
ON t.first_name = my_table.first_name
WHEN NOT MATCHED THEN 
INSERT (first_name) VALUES (t.first_name);

Hope this help you!

Jaykumar Patel
  • 26,836
  • 12
  • 74
  • 76