0

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?

AaA
  • 3,111
  • 8
  • 52
  • 77
CustomNet
  • 691
  • 3
  • 10
  • 30
  • Does this answer your question? [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) – warren Jul 27 '20 at 17:26

1 Answers1

1

INSERT statements support two1 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)
  1. 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.
ruakh
  • 156,364
  • 23
  • 244
  • 282
  • 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 – CustomNet Nov 19 '12 at 23:04
  • @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.) – ruakh Nov 19 '12 at 23:08