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