Insert where not exists

Posted: March 21st, 2008 | Author: | Filed under: Uncategorized | Tags: | 7 Comments »

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

7 Comments on “Insert where not exists”

  1. 1 Swap Insert for Update When Key Exists | Code Spatter said at 10:02 am on April 30th, 2008:

    [...] Insert where not exists by Tim Share and Enjoy: These icons link to social bookmarking sites where readers can share and [...]

  2. 2 tim brockman said at 1:52 am on March 23rd, 2010:

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

  3. 3 Tim said at 7:43 am on March 23rd, 2010:

    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)

  4. 4 Durdens said at 5:20 am on September 19th, 2010:

    This worked like a charm!, i tried using INSERT IGNORE INTO, but that wont work unless the whole row is an exact duplicate of a previous row! FROM DUAL WHERE NOT EXISTS rocks

  5. 5 beat the bookstore said at 12:37 am on January 17th, 2012:

    Together……

    [...]Without having any risk of getting impacted by recession[...]…

  6. 6 website url said at 5:33 am on January 17th, 2012:

    Gems form the internet…

    [...]very few websites that happen to be detailed below, from our point of view are undoubtedly well worth checking out[...]……

  7. 7 Brine Lacrosse Gloves said at 9:08 am on January 20th, 2012:

    Crazy Ideas…

    …Why do so many visitors disagree……

Leave a Reply

You must be logged in to post a comment.