Tim on March 21st, 2008

If you want to insert values into a MySQL table, but only if those values don’t already exist, and you don’t want to use a primary key on the table (and deal with the resulting error suppression ), here’s an elegant one-query method of doing so.

INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL
WHERE NOT EXISTS(
    SELECT [column1] FROM [same table name]
    WHERE [column1]='[value1]'
    AND [column2]='[value2]' LIMIT 1
)

OK, technically it’s not a single query. But it is only one round trip to the MySQL server.

Hat tip to Matt Mongeau

3 Responses to “Insert where not exists”

  1. Not exactly looking for this query but, it’s always cool to see another Orlando person writing the code. Good all the same, Cheers.

  2. Good to know you too! There’s quite a few of us. You should come out to the local events: Florida Creatives, ORUG, Likemind, and all the rest. Check out Ryan’s event calendar: http://upcoming.yahoo.com/group/2967/

    (Tip: I’m actually not going to be an Orlando-ian for much longer…moving to San Francisco at the end of April)

Trackbacks/Pingbacks

  1. Swap Insert for Update When Key Exists | Code Spatter

Leave a Reply

You will be able to edit your comment after submitting.