MySQL on duplicate key update

Question

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’

Answer 1

What about variables: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Sample:

SET @value = 'xxx';
INSERT INTO TABLENAME(col1, col2) VALUES (@value, ‘yyy’) ON DUPLICATE KEY UPDATE col1 = @value

And why do you exactly need this? Do you type the queries in phpMyAdmin by hand?

Answer 2

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.