"if not exists" SQL Statement Giving Error

Question

I have the following sql query running and it is giving me error 1064, syntax error.

IF NOT EXISTS (select * from locations where STREET_ADDRESS = 'test') 
BEGIN
    insert into locations (STREET_ADDRESS) values ('test') 
end;

Can someone please help me out? It seems so simple yet it will not run. Thanks.

Also, I'm running MySQL version 5.6.11

values, where, from, select, inset, into must be Upper try it — user3173819, Mar 10 '14 at 00:30
@user3173819 No; MySql is case-insensitive, at least my version is. Thank you for the suggestion though. — Aaron, Mar 10 '14 at 00:34
you should follow this link as “if not exists” [1]: http://stackoverflow.com/a/3164741/3242978 — jmail, Mar 10 '14 at 07:23

score 0 · Answer 1 · answered Mar 10 '14 at 01:08

0

Also you can try this

INSERT INTO locations (STREET_ADDRESS)
SELECT 'test' FROM DUAL
WHERE NOT EXISTS (
   SELECT * from locations where STREET_ADDRESS = 'test'
) LIMIT 1;

answered Mar 10 '14 at 01:08

Sky

3,330
1
12
12

score 0 · Answer 2 · answered Mar 10 '14 at 01:29

0

Try this

IF ((select * from locations where STREET_ADDRESS = 'test')=0 )
BEGIN
insert into locations (STREET_ADDRESS) values ('test') 
end;

answered Mar 10 '14 at 01:29

pareto

186
5

score 0 · Answer 3 · edited May 23 '17 at 12:21

0

you should use this type of sample code as:

   INSERT INTO table_listnames (name, address, tele)
    SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM table_listnames WHERE name = 'Rupert'
    ) LIMIT 1;

click here

edited May 23 '17 at 12:21

Community

1
1

answered Mar 10 '14 at 08:57

jmail

5,308
3
16
32

"if not exists" SQL Statement Giving Error

3 Answers3

Linked