Is There an SQL Server Equivalent to MySQL's ON DUPLICATE KEY UPDATE?

Does SQL Server Offer a Feature Similar to MySQL's ON DUPLICATE KEY UPDATE?

In MySQL, ON DUPLICATE KEY UPDATE allows for the automatic update of a row when an insert would cause a duplicate value in a unique index or primary key. In SQL Server, the closest equivalent to this feature is the MERGE statement.

Using MERGE to Update or Insert

The MERGE statement combines the functionality of INSERT, UPDATE, and DELETE statements into a single operation. It can be used to:

• Update existing rows that match a specified condition
• Insert new rows if no matching rows are found

Example

Consider the following query, which attempts to insert a row into a table if the row doesn't already exist:

INSERT INTO MyTable (Id, Name) VALUES (1, 'John Doe')

If a row with Id 1 already exists, this query will fail due to a primary key violation. However, we can use MERGE to handle this scenario:

MERGE INTO MyTable WITH (HOLDLOCK) AS target
USING (SELECT 1 AS Id, 'John Doe' AS Name) AS source
(Id, Name)
ON (target.Id = source.Id)
WHEN MATCHED
  THEN UPDATE
  SET Name = source.Name
WHEN NOT MATCHED
  THEN INSERT (Id, Name)
  VALUES (source.Id, source.Name);

In this example, the MERGE statement will perform the following actions:

• Check if a row with Id 1 exists in the target table.
• If a matching row exists, update the Name column to John Doe.
• If no matching row exists, insert a new row with Id 1 and Name John Doe.

The WITH (HOLDLOCK) clause ensures that the target table is locked during the operation to prevent concurrent updates from causing data consistency issues.

The above is the detailed content of Is There an SQL Server Equivalent to MySQL's ON DUPLICATE KEY UPDATE?. For more information, please follow other related articles on the PHP Chinese website!