3 ways to write UPSERT and MERGE queries in MySQL
This article is a quick pointer on MySQL’s three techniques for UPSERT (update/insert) or MERGE queries. I’ve written about this before, but since SQL 2003 has a MERGE statement (which Oracle and DB2 support), many people are asking for similar functionality in MySQL without realizing it already exists. I hope this article helps you find what you need.
The three tools are
REPLACEINSERT ... ON DUPLICATE KEY UPDATE ...- Transactional
UPDATEfollowed byINSERT
I’ve discussed them in great detail in my article on flexible insert and update statements in MySQL.
There are other methods too, such as INSERT IGNORE, but these are the three most important.
REPLACEdoes not do an update, it completely replaces the original row(s).Arjen Lentz
9 Jul 06 at 7:12 pm
After deleting them, though. So it’s actually a
DELETE/INSERTconstruct. I didn’t mean to imply it was anUPDATE. I just wanted to list out ways to get similar functionality.This is great that you’re commenting, by the way. One of my goals with this blog is to learn as much as possible. Hopefully it’s also useful to others, but it’s a great chance for me to get corrections where I need them.
Xaprb
9 Jul 06 at 8:42 pm
The “INSERT .. SELECT .. ON DUPLICATE KEY” can work *MUCH* faster than a similar “INSERT … SELECT .. GROUP BY” when there is not much duplication against the Primary/Unique key on the Inserted table.
The disk I/O is greatly increased but there are *NO* temporary tables involved greatly reducing RAM/Temp space requirements of the MySQL Server.
In a data warehousing world, the ON DUPLICATE KEY (UPSERT) is crutial !!
J Jorgenson
12 Apr 07 at 2:23 pm