3 ways to write UPSERT and MERGE queries in MySQLSat, Jun 17, 2006 in Databases
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
INSERT ... ON DUPLICATE KEY UPDATE ...
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.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.