CODEWITHSUNDEEP
mysqlinsert
CustomNet
CustomNet

Reputation: 652

MySQL insert where not exists / if not exists

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?

Upvotes: 1

Views: 7162

Answers (1)

ruakh
ruakh

Reputation: 183231

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.

Upvotes: 1

Related Questions