SQL
Performing an UPSERT (INSERT OR UPDATE) Operation
Efficiently insert a new record or update an existing one if a unique conflict occurs, using SQL's UPSERT mechanism to manage data integrity.
INSERT INTO users (email, username, password_hash, created_at, updated_at)
VALUES ('[email protected]', 'exampleuser', 'hashed_password_123', NOW(), NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
password_hash = EXCLUDED.password_hash,
updated_at = NOW();
How it works: This query performs an 'UPSERT' operation, which means 'INSERT if not exists, UPDATE if exists'. It attempts to insert a new user record. If a row with the same `email` (assuming `email` is a unique constraint) already exists, it updates the `username`, `password_hash`, and `updated_at` fields of the existing record instead. This is crucial for preventing duplicate entries and managing data synchronization.