INSERT INTO ... ON DUPLICATE KEY ... UPDATE
This is a wiki page. Be bold and improve it!
If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.
INSERT INTO mytable (id, value) VALUES (1, 'cat') ON DUPLICATE KEY UPDATE value = 'cat';Beware that using an UPDATE then checking for the number of affected rows may not work as intended: with mysql at least, if a row does exist but the updated values are the same as the original ones, mysql will somewhat logically say that 0 rows were affected.
Portability
The statement above is valid for mysql, but may not be valid for all database engines. The only portable way to express the above statement is to break it into two, one SELECT and one INSERT if the SELECT returned 0 rows.