How to Perform upsert in SQL Server and MySQL

MERGE in SQL Server and ON DUPLICATE KEY UPDATE in MySQL

Write a SQL query to upsert data, using MERGE in SQL Server and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.

Solution:

-- SQL Server
MERGE INTO Employees AS Target
USING (VALUES (1, 'John Doe', 50000)) AS Source (EmployeeID, Name, Salary)
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Name = Source.Name, Salary = Source.Salary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Salary) VALUES (Source.EmployeeID, Source.Name, Source.Salary);

-- MySQL
INSERT INTO Employees (EmployeeID, Name, Salary)
VALUES (1, 'John Doe', 50000)
ON DUPLICATE KEY UPDATE Name = VALUES(Name), Salary = VALUES(Salary);

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how upsert operations differ between SQL Server and MySQL.
• Key Components :
• MERGE (SQL Server): Combines insert and update operations.
• ON DUPLICATE KEY UPDATE (MySQL): Updates existing rows or inserts new ones.
• Why Compare Upsert Operations?:
• Upsert functionality varies across platforms, impacting query portability.
• Understanding these differences ensures efficient data management.
• Real-World Application :
• In ETL processes, upserts synchronize data between systems.

Additional Notes:

• Use MERGE in SQL Server and ON DUPLICATE KEY UPDATE in MySQL.
• Test queries on all target platforms to ensure consistent results.
• Important Considerations:
• Ensure unique constraints for proper upsert behavior.

For more Practice: Solve these Related Problems:

• Write a SQL query to upsert customer data, using MERGE in SQL Server and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.
• Write a SQL query to upsert product data, using MERGE in SQL Server and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.
• Write a SQL query to upsert order data, using MERGE in SQL Server and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.
• Write a SQL query to upsert employee data, using MERGE in SQL Server and INSERT ... ON DUPLICATE KEY UPDATE in MySQL.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise:Writing a Query that uses GROUP_CONCAT in MySQL and STRING_AGG in PostgreSQL.
Next SQL Exercise: Writing a Query that uses ROW_NUMBER() Across Databases.