php - Can I use ON DUPLICATE KEY UPDATE with an INSERT query using the SET option?

Question

I've seen the following (using the VALUES option):

$query = "INSERT INTO $table (column-1, column-2, column-3) VALUES ('value-1', 'value-2', 'value-3') ON DUPLICATE KEY UPDATE SET column1 = value1, column2 = value2, column3 = value3, ID=LAST_INSERT_ID(ID)"; 

... but I can't figure how to add ON DUPLICATE KEY UPDATE to what I'm using:

$query = "INSERT INTO $table SET
    column-1 ='value-1',
    column-2 ='value-2',
    column-3 ='value-3'
";

e.g.:, pseudo-code

$query = "INSERT INTO $table SET
    column-1 ='value-1',
    column-2 ='value-2',
    column-3 ='value-3'
    ON DUPLICATE KEY UPDATE SET
    column1 = value1,
    column2 = value2,
    column3 = value3,
    $id=LAST_INSERT_ID(id)"; 
    $my_id = mysql_insert_id();
";

I would find the latter easier to read. Would appreciate clarification, didn't find an example in the manual.

cheers

Answer 1

I've used ON DUPLICATE KEY UPDATE a lot. For some situations it's non-standard SQL extension that's really worth using.

First, you need to make sure you have a unique key constraint in place. The ON DUPLICATE KEY UPDATE function only kicks in if there would've been a unique key violation.

Here's a commonly used format:

 $query = "INSERT INTO $table (column1, column2, column3)
 VALUES ('value-1', 'value-2', 'value-3')
 ON DUPLICATE KEY UPDATE
 column1 = values(column1),
 column2 = values(column2),
 column3 = values(column3);"

column1 = values(column1) means "Update column1 with the value that would have been inserted if the query hadn't hit the duplicate key violation." In other words, it just means update column1 to what it would've been had the insert worked.

Looking at this code, it doesn't seem correct that you're updating all three of the columns you're trying to insert. Which of the columns has a unique constraint on it?

EDIT: Modify based on 'SET' format of mysql insert statement per the question from the OP.

Basically to use ON DUPLICATE KEY UPDATE, you just write the insert statement as you normally would, but add the ON DUPLICATE KEY UPDATE clause tacked onto the end. I believe it should work like this:

INSERT INTO $table 
    set column1 = 'value-1',
        column2 = 'value-2',
        column3 = 'value-3'
ON DUPLICATE KEY UPDATE
    column1 = values(column1),
    column2 = values(column2),
    column3 = values(column3);

Again, one of the columns you're inserting has to have a unique index (or a combination of the columns). That can be because one of them is the primary key or because there is a unique index on the table.