When synchronizing tables or databases, sometimes it is useful to have a way to update a row if there is already an entry for a key. Using the MySQL syntax, ON DUPLICATE KEY UPDATE, it is possible to insert a row if it doesn’t exist or update it if there is already a row with that key.

INSERT INTO table1 (
	id,
	text,
	sync_time
)
VALUES  (
	1,
	'new',
	UNIX_TIMESTAMP()
)
ON DUPLICATE KEY UPDATE
	text		= VALUES(text),
	sync_time	= VALUES(sync_time)

To update table1 with all of table2’s data, a single query can be run without the need of any programming logic.

INSERT INTO table1 (id, text, sync_time)
(SELECT id, text, sync_time FROM table2)
ON DUPLICATE KEY UPDATE
	text		= VALUES(text),
	sync_time	= VALUES(sync_time)

For More MySQL Tips

Check out these other posts: