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)

I use webfaction to host a lot of my django projects. It has an easy setup that will get you developing quickly and a great community of talented programmers. There is also a quick setup for rails, wordpress, and a lot more.

Related posts:

  1. Careful with Numeric Data Types in MySQL  TINYINT can be set to length of 4, but that doesn’t mean the max value is 9999. A tiny int...
  2. Conditions on Count or Sum in MySQL  When selecting count, sum, or some other aggregate function, the value isn’t determined until after the WHERE clause so a...
  3. Tips for MySQL to PostgreSQL Switch  If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get...