SQL
Perform UPSERT (INSERT or UPDATE) in SQL
Learn to implement UPSERT logic (insert new record or update existing) in SQL, critical for managing unique data entries without duplicates.
INSERT INTO
user_settings (user_id, theme_color, notifications_enabled)
VALUES
(123, 'dark', TRUE)
ON CONFLICT (user_id) DO UPDATE
SET
theme_color = EXCLUDED.theme_color,
notifications_enabled = EXCLUDED.notifications_enabled;
How it works: This snippet demonstrates an UPSERT operation, specifically using PostgreSQL's `ON CONFLICT` syntax. It attempts to insert a new row into `user_settings`. If a row with the given `user_id` already exists (due to a unique constraint), it updates the `theme_color` and `notifications_enabled` columns of the existing row with the new values. Other database systems like MySQL use `INSERT ... ON DUPLICATE KEY UPDATE`.