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

SQL Server's Counterpart to MySQL's ON DUPLICATE KEY UPDATE

In MySQL, the ON DUPLICATE KEY UPDATE clause allows users to update existing rows instead of inserting new ones if a duplicate key is encountered. This feature is particularly useful when dealing with unique constraints and primary keys.

SQL Server's MERGE Statement

While SQL Server does not have an exact equivalent to MySQL's ON DUPLICATE KEY UPDATE, it does provide the MERGE statement as a comparable solution. MERGE combines the functionality of INSERT, UPDATE, and DELETE statements into a single query, allowing for efficient handling of duplicate values.

Implementing ON DUPLICATE KEY UPDATE Using MERGE

To implement the on-duplicate-update behavior in SQL Server, you can use the following syntax:

MERGE INTO [target_table] AS target
USING [source_table] AS source
ON (target.[matching_column] = source.[matching_column])
WHEN MATCHED THEN
    UPDATE SET [target_column] = [source_column]
WHEN NOT MATCHED THEN
    INSERT ([target_column]) VALUES ([source_column]);

Example

Consider the following query that inserts data into a table named METER_DATA and updates row values if a duplicate exists based on the rtu_id and time_local columns:

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

This query will insert the values from the source table into the target table if a row with the same rtu_id and time_local does not already exist. If a duplicate row does exist, the update will be performed instead, preserving the existing data in the target table.

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