Swap Insert for Update When Key Exists
Posted on April 30th, 2008 by Greg Allard in MySQL | Comments
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:
- How To Use Triggers to Track Changes in MySQL Setting constraints and rules in the database is better than writing special code to handle the same task since it...
- Multiple Inserts with a Subquery Inserting multiple rows into a table is simple. INSERT INTO table1 (id) VALUES (1), (2) But what if you want...
- How to Break a MySQL Left Join Care must be taken when placing conditions on the results of the right-hand table of a LEFT JOIN because it...