Insert a new row if it doesn't already exist based on non-unique columns

Question

I know about INSERT ... ON DUPLICATE KEY UPDATE but that won't work because the criteria that I will use to check for an existing row is not a unique or primary key. In fact, it will be 2 different fields. It couldn't even be unique key because the values will not be unique.

This is my dummy insert statement

INSERT INTO 
    videos 
    (
        first_name, 
        last_name, 
        addresss,
        plan
    )
VALUES 
    (
        :first_name, 
        :last_name, 
        :addresss,
        :plan
    )

So I want to say if there's a row already WHERE last_name = :last_name AND plan = :plan, then update it, otherwise, add a new row.

I could do this with multiple queries, but I'm just trying to see if there's a way to do this with one shot without going back and forth with it. This will be done in a loop, so if I have to do it in multiple queries it will probably double the number of queries.

Please read this article: http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — James McDonnell, Jan 28 '14 at 00:14

Gordon Linoff · Accepted Answer · 2014-01-28T01:43:13.923

0

Just define a unique index on the two columns:

create unique index videos_lastname_plan on videos(lastname, plan);

The documentation clearly states:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

So, a unique index on two clumns is perfectly fine. The statement is:

INSERT INTO videos (first_name, last_name, addresss, plan)
    VALUES (:first_name, :last_name, :addresss, :plan)
    on duplicate key update address = VALUES(address), first_name = VALUES(first_name);

edited Jan 28 '14 at 01:43

answered Jan 28 '14 at 00:30

Gordon Linoff

1,242,037
58
646
786

Gotcha I get it now. In the last line, instead of plan, it should be first_name, right? Since plan is part of the unique index. – zen Jan 28 '14 at 01:09
@zen . . . Yes. I was thinking the first two are the unique key. Fixed. – Gordon Linoff Jan 28 '14 at 01:43
what if I have a auto incrementing `PRIMARY` column for the ID. So essentially a PRIMARY AI and the UNIQUE index we created. Will the `ON DUPLICATE KEY UPDATE` be effected by the PRIMARY id in any way? I'm assuming since its auto incrementing it should ignore it? – zen Jan 30 '14 at 06:36
1

@zen . . . The `on duplicate update` checks *all* unique keys, including the primary key. However, an auto-incrementing key is never going to cause a problem. In other words, it is checked, but won't be an issue. – Gordon Linoff Jan 30 '14 at 14:10

Insert a new row if it doesn't already exist based on non-unique columns

1 Answers1