SQL if not exists

Question

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.

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
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

Jay Patel · Answer 1 · 2014-04-03T12:31:14.760

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!

edited Apr 03 '14 at 12:31

answered Apr 03 '14 at 12:15

Jay Patel

23,885
12
63
74

May be I am didnt get it. But what is `users` in your query? – Rahul Tripathi Apr 03 '14 at 12:18
sorry its my mistaken. – Jay Patel Apr 03 '14 at 12:21
1

Also to add that your first query is not different from what OP posted. That would not work! – Rahul Tripathi Apr 03 '14 at 12:22
Double check i'm suggest to remove column name (first_name). – Jay Patel Apr 03 '14 at 12:25
That would not create any difference if the user has only one column. Infact it is necessary if the table has more than one column. Your first query is wrong! – Rahul Tripathi Apr 03 '14 at 12:28
i think OP's got solution. and as per ur suggest i remove first query. Now you can check merge query work or not and give me reply. because i m tested. – Jay Patel Apr 03 '14 at 12:30

SQL if not exists

1 Answers1