Insert where PK doesn't exist

[Antnee]

Guys,

I need to run through a simple table as follows:

userid (int) (PK)
setting (varchar(50)) (PK)
value (varchar(50))

I need to add the setting 'scentre' and value 'checked' for each distinct userid where this row doesn't already exist. I'm sure it's a really simple query but I can't for the life of me work it out

[guido2004]

Do the insert. If the row already exists, an error will be thrown. Ignore it.

[Antnee]

OK, and the best way to do that is to...?

I want to do it all in one query. Can I do a sub-query from the same table in the userid insert?

[guido2004]

Sorry, I didn't understand your question correctly.
Try this

INSERT INTO tablename
SELECT 
    a.userid
  , 'scentre' 
  , 'checked'
FROM tablename a
LEFT OUTER JOIN tablename b
ON  a.userid = b.userid
AND a.setting = b.setting
AND b.setting = 'scentre'
WHERE b.userid IS NULL

[longneck]

put the keyword IGNORE between INSERT and INTO. this will cause the duplicates to silently fail.

[Antnee]

Thanks to you both. I was wondering how to deal with the duplicate key error. Brilliant, thanks. Learn something new every time