MySQL insert where not exists / if not exists

Question

I have tried the following query:

INSERT INTO `surfed_site` (user, site)
VALUES ('123', '456')
WHERE NOT EXISTS (SELECT site FROM `surfed_site` WHERE site=456)

But I keep getting a MySQL error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT site FROM surfed_site WHERE site=456)' at line 3

I have no clue what I'm doing wrong, would anybody be able to guide me?

Does this answer your question? How to 'insert if not exists' in MySQL? — warren
– warren, Commented Jul 27, 2020 at 17:26

ruakh · Accepted Answer · 2012-11-19 23:07:44Z

1

INSERT statements support two¹ syntaxes: one that uses VALUES, and one that uses a query. You can't combine them, and only the query syntax supports WHERE clauses. So:

INSERT INTO `surfed_site` (user, site)
SELECT '123', '456' FROM (SELECT 1) t
WHERE NOT EXISTS (SELECT site FROM `surfed_site` WHERE site=456)

Actually three syntaxes; you can also use SET. If you're only inserting one record, this one is functionally equivalent to VALUES, but arguably more readable.

edited Nov 19, 2012 at 23:07

answered Nov 19, 2012 at 23:02

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CustomNet Over a year ago

That doesn't seem to be working for me, I am getting the error message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT site FROM surfed_site WHERE site=456)' at line 3

ruakh Over a year ago

@LiamGordon: Oh, sorry. I've edited my answer to give what's hopefully a fixed version -- not tested. (Sorry, if nothing else I should have indicated that I hadn't tested what I posted.)

Collectives™ on Stack Overflow

MySQL insert where not exists / if not exists

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related