2

I am creating an SQL script that will add specific entries to the DB when run on a new environment. But I want to have to INSERT query insure that the data doesn't already exist before trying to insert it.

The query I came up with looks sound to me. But for some reason it gives me a syntax error. And yes I have RTFM'ed it. And I have Googled it. Based on other examples that I have seen (even here on Stack Exchange) it looks right to me. Can anyone identify what I am doing wrong?

Query:

INSERT INTO my_model (status, name) VALUES ('active', 'bbc')
WHERE NOT EXISTS (SELECT name FROM my_model WHERE name = 'bbc')

Any ideas?

Patrick
  • 3,302
  • 4
  • 28
  • 47
  • You can't do that. You could try using MERGE: http://stackoverflow.com/questions/545593/how-to-insert-with-where-clause – Lawson Oct 15 '13 at 15:27
  • 1
    general tip: if you get an error message, show it to us. Then RTFM: http://dev.mysql.com/doc/refman/5.6/en/insert.html `INSERT` does NOT have a `WHERE` clause. – Marc B Oct 15 '13 at 15:27
  • Is `name` a unique key? – Explosion Pills Oct 15 '13 at 15:28
  • Does your `name` column have a unique index on it? If so, you could use the [`INSERT ... ON DUPLICATE KEY UPDATE` syntax](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) – Rowland Shaw Oct 15 '13 at 15:28

4 Answers4

4

The plain INSERT doesn't allow a WHERE. If the primary key is (status, name) then use INSERT IGNORE as @aynber exists. If not, try INSERT ... SELECT:

INSERT INTO my_model (status, name)
SELECT 'active', 'bbc'
FROM DUAL
WHERE NOT EXISTS (SELECT name FROM my_model WHERE name = 'bbc');

DUAL is a system-supplied one-row table, used to guarantee one row of results for something like SELECT 'active', 'bbc' FROM DUAL. It's an Oracle "invention" but supported in MySQL. You may be able to drop the FROM DUAL from the query; I don't have MySQL today so I can't test it to be sure.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • I ended up using this: INSERT INTO my_model (active_status, name) SELECT 'active', 'bbc' FROM DUAL WHERE NOT EXISTS (SELECT name FROM my_model WHERE name = 'bbc') – Patrick Oct 15 '13 at 16:00
1

try this:

INSERT INTO my_model (status, name) select('active', 'bbc') from my_model
WHERE NOT EXISTS (SELECT name FROM my_model WHERE name = 'bbc');
ohmygirl
  • 239
  • 1
  • 5
0

You want INSERT IGNORE instead of NOT EXISTS, if name is a primary/unique key. This will not insert anything if it hits a duplicate key error. You can also use ON DUPLICATE KEY UPDATE if you want to update a column if the row already exists.

aynber
  • 22,380
  • 8
  • 50
  • 63
0

in sql i would create a stored procedure not sure if you can in mysql

if !(SELECT name FROM my_model WHERE name = 'bbc') BEGIN INSERT INTO my_model (status, name) VALUES ('active', 'bbc') END

user2615302
  • 194
  • 2
  • 14