From Wikipedia, the free encyclopedia - View original article
A relational database management system uses SQL
MERGE (also called upsert) statements to
INSERT new records or
UPDATE existing records depending on whether or not a condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.
MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
Right join is employed over the Target (the INTO table) and the Source (the USING table / view / sub-query). That is:
If multiple Source rows match a given Target row, an error is mandated by SQL:2003 standards. You cannot update a Target row multiple times with a MERGE statement
Some database implementations adopted the term "Upsert" (a portmanteau of update and insert) to a database statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. It is also used to abbreviate the "MERGE" equivalent pseudo-code.
It is used in Microsoft SQL Azure.
Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.
MySQL, for example, supports the use of
INSERT ... ON DUPLICATE KEY UPDATE syntax which can be used to achieve a similar effect with the limitation that the join between target and source has to be made only on PRIMARY KEY or UNIQUE constraints, which is not required in the ANSI/ISO standard. It also supports
REPLACE INTO syntax, which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an
IGNORE clause for the
INSERT statement, which tells the server to ignore "duplicate key" errors and go on (existing rows will not be inserted or updated, but all new rows will be inserted).
MERGE INTO though fails to throw an error when there are multiple Source data rows. Additionally there is a single-row version,
UPDATE OR INSERT INTO tablename (COLUMNS) VALUES (VALUES) [MATCHING (COLUMNS)], but the latter does not give you the option to take different actions on insert versus update (e.g. setting a new sequence value only for new rows, not for existing ones.)
Microsoft SQL extends with supporting guards and also with supporting Left Join via
WHEN NOT MATCHED BY SOURCE clauses.