How Does SQL Server Handle Duplicate Keys Like MySQL's ON DUPLICATE KEY UPDATE?

Does SQL Server Offer an Equivalent to MySQL's ON DUPLICATE KEY UPDATE?

In MySQL, ON DUPLICATE KEY UPDATE allows for the seamless handling of duplicate key values by updating existing rows instead of inserting new ones. This feature is particularly useful when managing unique or primary key constraints.

SQL Server provides a comparable functionality through its MERGE statement. The MERGE statement combines the INSERT and UPDATE operations into a single command, allowing developers to insert new rows or update existing ones based on specified criteria.

For example, the following MERGE statement demonstrates how to insert a new row into the METER_DATA table only if the combination of rtu_id and time_local does not already exist. If a duplicate exists, it updates the row:

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');

The WHEN MATCHED clause performs the update operation on the existing row that satisfies the specified condition. The WHEN NOT MATCHED clause performs the insert operation for a new row that does not satisfy the condition.

By utilizing the MERGE statement, SQL Server provides a comprehensive feature similar to MySQL's ON DUPLICATE KEY UPDATE, enabling efficient handling of duplicate key values.

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