SQL
Upserting Data: Insert New Row or Update Existing on Conflict
Efficiently manage database records by performing an 'upsert' operation. Insert a new row or update an existing one based on a unique key conflict.
INSERT INTO user_preferences (user_id, theme, newsletter_subscribed)
VALUES (101, 'dark', TRUE)
ON CONFLICT (user_id) DO UPDATE SET
theme = EXCLUDED.theme,
newsletter_subscribed = EXCLUDED.newsletter_subscribed;
How it works: This snippet demonstrates an 'upsert' operation, which is common in PostgreSQL (similar syntax exists in MySQL with `ON DUPLICATE KEY UPDATE`). It attempts to insert a new row into `user_preferences`. If a conflict occurs on the `user_id` (which is typically a unique or primary key), the `ON CONFLICT (user_id) DO UPDATE` clause is triggered. Instead of failing, it updates the existing row's `theme` and `newsletter_subscribed` columns with the values from the current `INSERT` attempt, referenced via `EXCLUDED`.