Can I use ON DUPLICATE KEY UPDATE with an INSERT query using the SET option?
Solution 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.
Solution 2
Have you tried using REPLACE INTO
instead?
http://www.mysqlperformanceblog.com/2007/01/18/insert-on-duplicate-key-update-and-replace-into/
shecky
Updated on July 09, 2022Comments
-
shecky almost 2 years
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
-
shecky over 12 yearsThanks Kemal. REPLACE seems also like an appropriate method however the distinctions between REPLACE & ON DUPLICATE KEY UPDATE is not entirely clear to me yet. I'll read up on REPLACE.
-
shecky over 12 yearsThanks Kevin. My ID column is Primary Key and has Unique Index. I believe I understand how ON DUPLICATE KEY works, but my problem is adding it to a different INSERT syntax, one using SET as opposed to VALUES. I'd like to see an example of that syntax if it's doable.
-
shecky over 12 yearsP.s. my understanding is that REPLACE could potentially muck with the indexing in an unwelcome way so I'm going to try to make ON DUPLICATE KEY UPDATE work. Thx again Kemal
-
shecky over 12 yearsand just to clarify: I want to use ON DUPLICATE KEY UPDATE to prevent dupes added on refresh, though I'm using a header to redirect user to result page but I can't stop user from going back and re-submitting ... so yes I thought I'd UPDATE all fields if the ID exists
-
Kevin Bedell over 12 yearsI've modified the example to use the format you're asking about, at least I think I did!
-
shecky over 12 yearsThank you again. I've been trying to make this work for days but I can't. Either my understanding of ON DUPLICATE KEY UPDATE is very wrong or there's something amiss in my flow. I'll either edit my question or re-post with more context. Thx
-
Kevin Bedell over 12 yearsWould you mind accepting this answer then and maybe sending me a copy of your code and the error it generates?
-
shecky over 12 yearsaccepted ... and I'm presently hacking at it. I'll edit this question hopefully before end of day, cheers.
-
frazras about 9 yearssee details about replace here stackoverflow.com/questions/4205181/…