SQL
Performing Upsert (Insert or Update) Operations in SQL
Learn how to perform an "upsert" operation in SQL, which inserts a row if it doesn't exist or updates it if a matching record is found, crucial for data synchronization.
-- For PostgreSQL (using ON CONFLICT DO UPDATE)
INSERT INTO users (email, username, last_login)
VALUES ('[email protected]', 'testuser', NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login = EXCLUDED.last_login;
-- For MySQL (using INSERT ... ON DUPLICATE KEY UPDATE)
-- Requires a UNIQUE index on the `email` column
INSERT INTO users (email, username, last_login)
VALUES ('[email protected]', 'testuser', NOW())
ON DUPLICATE KEY UPDATE
username = VALUES(username),
last_login = VALUES(last_login);
-- For SQL Server (using MERGE statement, more complex but powerful)
-- This requires a target table, source data, and specific matching logic.
-- Due to complexity, a simplified example for illustration:
-- MERGE INTO target_table AS Target
-- USING (VALUES ('value1', 'value2')) AS Source (col1, col2)
-- ON Target.col1 = Source.col1
-- WHEN MATCHED THEN
-- UPDATE SET Target.col2 = Source.col2
-- WHEN NOT MATCHED THEN
-- INSERT (col1, col2) VALUES (Source.col1, Source.col2);
How it works: This snippet demonstrates "upsert" operations, a powerful technique to either `INSERT` a new record or `UPDATE` an existing one based on a unique key. For PostgreSQL, `ON CONFLICT (column_name) DO UPDATE SET ...` is used, where `EXCLUDED` refers to the values that would have been inserted. MySQL utilizes `INSERT ... ON DUPLICATE KEY UPDATE ...`, requiring a unique index on the key column and using `VALUES()` to reference the new values. While SQL Server offers the `MERGE` statement for similar functionality, it's more verbose. These methods are invaluable for synchronizing data, handling user registrations, or managing inventory where you want to avoid duplicates and ensure records are always up-to-date.