CODEWITHSUNDEEP
mysqlduplicates
Java Coder
Java Coder

Reputation:

MySQL on duplicate key update

If I have query like this, how can I refer to values I have already given in update statement, so that I don't need to insert same data to query again? Example I would like to update col1 value with 'xxx', but now I need to enter 'xxx' again in duplicate statement. Is there anyway to refer those values in duplicate statement?

INSERT INTO TABLENAME(col1, col2) 
VALUES (’xxx’, ‘yyy’) 
ON DUPLICATE KEY UPDATE col1 = ‘zzz’

Upvotes: 9

Views: 25130

Answers (2)

Joshua Martell
Joshua Martell

Reputation: 7202

This should work and is a little more elegant:

INSERT INTO TABLENAME(col1, col2) 
VALUES ('xxx', 'yyy') 
ON DUPLICATE KEY UPDATE col1 = VALUES(col1)

Note that you don't need to update the primary key part of the row. We know that's the same because there was a collision.

Upvotes: 33

Marc Armstrong
Marc Armstrong

Reputation: 39

In PHP I do this to solve the issue:

$fields = "a = 1, b = 2, c = 3";
$sql = "INSERT INTO some_table SET id = $id, $fields ON DUPLICATE KEY UPDATE $fields";
mysql_query($sql);

Upvotes: 3

Related Questions