SQL
Efficiently Insert or Update Records (UPSERT) in SQL
Learn to perform atomic 'upsert' operations in SQL using INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) or REPLACE INTO (MySQL) to manage data.
-- PostgreSQL/SQLite (requires a unique constraint on 'email')
INSERT INTO users (username, email, last_login_at)
VALUES ('johndoe', '[email protected]', NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login_at = EXCLUDED.last_login_at;
-- MySQL (using REPLACE INTO, behaves like DELETE + INSERT)
REPLACE INTO users (username, email, last_login_at)
VALUES ('janedoe', '[email protected]', NOW());
-- SQL Server (using MERGE)
MERGE INTO users AS target
USING (VALUES ('newuser', '[email protected]', GETDATE())) AS source (username, email, last_login_at)
ON target.email = source.email
WHEN MATCHED THEN
UPDATE SET
username = source.username,
last_login_at = source.last_login_at
WHEN NOT MATCHED THEN
INSERT (username, email, last_login_at)
VALUES (source.username, source.email, source.last_login_at);
How it works: The 'upsert' operation is crucial for web applications that need to insert a row if it doesn't exist or update it if it does. This snippet provides examples for different SQL databases. PostgreSQL and SQLite use `INSERT ... ON CONFLICT (column) DO UPDATE SET ...`, which is a robust, atomic way to handle conflicts based on a unique constraint. MySQL uses `REPLACE INTO`, which first tries to delete any existing row matching a primary or unique key and then inserts the new row. SQL Server offers the powerful `MERGE` statement for complex insert/update/delete logic. Choosing the right method depends on your database system and specific requirements.