在插入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?

Answer 1

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.