Swap Insert for Update When Key Exists
Posted on April 30th, 2008 by Greg Allard in MySQL |
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:
- 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...
- 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...
- 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...
For the last six years, Greg Allard has been a web developer for UCF.
Over the years,