2

My aim

I'm trying to perform the following sql query:

INSERT INTO api(site,key) VALUES ('example.com','a-random-key') WHERE NOT EXISTS (SELECT * FROM api WHERE site='example.com');

In order to insert a new row with a site and a corresponding key if there isn't already one with that site (eg insert 'example.com' with the associated key 'a-random-key' if there isn't an other record with the site 'example.com').

What isn't working

I'm getting the following 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 * FROM api WHERE site='example.com')' at line 1

What I've tried

I've tried to perform the two queries separatedly, and they both work.

My question

How can I make the query work?

Community
  • 1
  • 1
Stubborn
  • 995
  • 4
  • 17
  • 30
  • Where clause cannot be used in insert query also for your problem add unique index on site column – M Khalid Junaid Jan 10 '15 at 19:55
  • [I'd read that it's possible](http://stackoverflow.com/a/913929/4086503), I hadn't noticed about the first "select". Thanks for your suggestion: following it, will I be able to check if the record was actually inserted or not using PHP's mysqli_num_rows()? – Stubborn Jan 10 '15 at 20:00
  • 1
    See the linked answer again where clause is used for select statement not for insert – M Khalid Junaid Jan 10 '15 at 20:01
  • Yeah I've just noticed about that, you're right, thanks for your help – Stubborn Jan 10 '15 at 20:02

2 Answers2

3

As people are suggesting, the better approach would be to add a unique index on site column (that way it wouldn't be inserted if site exists). Anyway, if that's not an option, I'd try this query:

INSERT INTO api(site, `key`)
SELECT * FROM (SELECT 'example.com','a-random-key') AS tmp
WHERE NOT EXISTS (SELECT * FROM api WHERE site='example.com') LIMIT 1;
acontell
  • 6,792
  • 1
  • 19
  • 32
  • Thanks, that works! If I add a unique index, will I be able to check if the record was actually inserted or not using PHP's mysqli_num_rows() or will I have to use something else? – Stubborn Jan 10 '15 at 20:06
  • @Stubborn, if you add a unique index then the record won't be inserted - you'll probably want to trap the error raised. – David Faber Jan 10 '15 at 20:08
  • My apologies, it works, but in SQL Fiddle at least `key` has to be surrounded by backticks – David Faber Jan 10 '15 at 20:10
  • @DavidFaber Thanks :) I'm going to update the answer with the backticks – acontell Jan 10 '15 at 20:14
2

I think you want to try something like this:

INSERT INTO api (site, `key`)
SELECT 'example.com','a-random-key'
  FROM dual
 WHERE NOT EXISTS (SELECT * FROM api WHERE site='example.com');

I don't think WHERE NOT EXISTS can be used in an INSERT query without a select. Please see SQL Fiddle demo here. Also, the above SELECT statement doesn't work if you don't use FROM dual.

UPDATE A second issue is that the column key should be surrounded by backticks ... I think MySQL is choking on it because it is a reserved word.

David Faber
  • 12,277
  • 2
  • 29
  • 40