How Does SQL Server's MERGE Statement Compare to MySQL's ON DUPLICATE KEY UPDATE?

SQL Server's MERGE Statement: A Versatile Alternative to MySQL's ON DUPLICATE KEY UPDATE

In MySQL, the ON DUPLICATE KEY UPDATE clause allows for the modification of existing rows when inserting data into a table with a UNIQUE index or PRIMARY KEY. While SQL Server does not have an exact equivalent, the MERGE statement offers a powerful and flexible solution for handling duplicate data.

Introducing the MERGE Statement

The MERGE statement combines the functionality of INSERT and UPDATE statements into a single operation. It evaluates a specified condition to determine whether to insert a new row or update an existing one. The syntax for a basic MERGE statement is:

MERGE INTO target_table AS target
USING source_table AS source
ON (target.column_name = source.column_name)
WHEN MATCHED THEN
    UPDATE SET target.column_name = source.column_name
WHEN NOT MATCHED THEN
    INSERT (target.column_names) VALUES (source.column_values)

Example Usage

To illustrate the use of the MERGE statement to handle duplicate data, consider the following example:

MERGE
INTO MyBigDB.dbo.METER_DATA WITH (HOLDLOCK) AS target
USING (SELECT
    77748 AS rtu_id
   ,'12B096876' AS meter_id
   ,56112 AS meter_reading
   ,'20150602 00:20:11' AS time_local) AS source
(rtu_id, meter_id, meter_reading, time_local)
ON (target.rtu_id = source.rtu_id
  AND target.time_local = source.time_local)
WHEN MATCHED
  THEN UPDATE
      SET meter_id = '12B096876'
         ,meter_reading = 56112
WHEN NOT MATCHED
  THEN INSERT (rtu_id, meter_id, meter_reading, time_local)
      VALUES (77748, '12B096876', 56112, '20150602 00:20:11');

In this example, the MERGE statement attempts to insert a new row into the MyBigDB.dbo.METER_DATA table using data from the source table. If a row with the same rtu_id and time_local values already exists, the statement updates that row with the specified values for meter_id and meter_reading. Otherwise, the statement inserts a new row into the table.

Advantages of the MERGE Statement

Over the ON DUPLICATE KEY UPDATE clause in MySQL, the MERGE statement in SQL Server offers several advantages:

• Versatility: The MERGE statement allows for more complex update operations by providing multiple WHEN MATCHED clauses to update different columns based on different conditions.
• Concurrency Control: The WITH (HOLDLOCK) option in the MERGE statement ensures that the target table is locked during the operation, preventing other concurrent transactions from modifying the affected rows.
• Bulk Updates: The MERGE statement can be used to perform bulk updates efficiently, reducing the number of database round-trips required for complex data manipulations.

The above is the detailed content of How Does SQL Server's MERGE Statement Compare to MySQL's ON DUPLICATE KEY UPDATE?. For more information, please follow other related articles on the PHP Chinese website!