SQL
Upsert (Insert or Update) Data
Learn how to perform an 'upsert' operation, inserting a new record if it doesn't exist, or updating an existing one, using database-specific syntax like `ON DUPLICATE KEY UPDATE` or `ON CONFLICT`.
-- MySQL Syntax:
INSERT INTO user_settings (user_id, theme, notifications_enabled)
VALUES (101, 'dark', TRUE)
ON DUPLICATE KEY UPDATE
theme = VALUES(theme),
notifications_enabled = VALUES(notifications_enabled);
-- PostgreSQL Syntax (assuming a UNIQUE constraint on user_id):
-- INSERT INTO user_settings (user_id, theme, notifications_enabled)
-- VALUES (101, 'dark', TRUE)
-- ON CONFLICT (user_id) DO UPDATE SET
-- theme = EXCLUDED.theme,
-- notifications_enabled = EXCLUDED.notifications_enabled;
How it works: This snippet demonstrates an 'upsert' operation, which is crucial for data synchronization. If a row with `user_id = 101` already exists in `user_settings`, its `theme` and `notifications_enabled` columns will be updated. If no such row exists, a new one will be inserted. The MySQL syntax uses `ON DUPLICATE KEY UPDATE` with `VALUES()`, while PostgreSQL uses `ON CONFLICT (column_name) DO UPDATE SET` with `EXCLUDED.column_name` to achieve this atomic insert-or-update behavior.