Checking for data before inserting into MySQL DB

Question

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?

You can't do that. You could try using MERGE: stackoverflow.com/questions/545593/… — Lawson
– Lawson, Commented Oct 15, 2013 at 15:27
general tip: if you get an error message, show it to us. Then RTFM: dev.mysql.com/doc/refman/5.6/en/insert.html INSERT does NOT have a WHERE clause. — Marc B
– Marc B, Commented Oct 15, 2013 at 15:27
Does your name column have a unique index on it? If so, you could use the INSERT ... ON DUPLICATE KEY UPDATE syntax — Rowland Shaw
– Rowland Shaw, Commented Oct 15, 2013 at 15:28

Ed Gibbs · Accepted Answer · 2013-10-15 15:32:31Z

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.

answered Oct 15, 2013 at 15:32

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

1 Comment

Patrick Over a year ago

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')

ohmygirl · Accepted Answer · 2013-10-15 15:31:12Z

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');

answered Oct 15, 2013 at 15:31

ohmygirl

2391 silver badge5 bronze badges

Comments

aynber · Accepted Answer · 2013-10-15 15:27:48Z

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.

answered Oct 15, 2013 at 15:27

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Comments

user2615302 · Accepted Answer · 2013-10-15 15:28:21Z

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

answered Oct 15, 2013 at 15:28

user2615302

1942 silver badges14 bronze badges

Collectives™ on Stack Overflow

Checking for data before inserting into MySQL DB

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related