SQL UPSERT – INSERT or UPDATE on Conflict

-- The unsafe manual approach (race condition between SELECT and INSERT)
SELECT id FROM user_settings WHERE user_id = 42;
-- If row exists: UPDATE ...
-- If not: INSERT ...

-- UPSERT solves this atomically in one statement

-- Upsert a user setting: insert if not present, update if exists
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', true)
ON CONFLICT (user_id)
DO UPDATE SET
    theme         = EXCLUDED.theme,
    notifications = EXCLUDED.notifications,
    updated_at    = NOW();
-- EXCLUDED.theme refers to the 'dark' value from the attempted INSERT

-- Increment a page view counter (insert on first visit, increment on subsequent)
INSERT INTO page_views (page_slug, view_count)
VALUES ('/home', 1)
ON CONFLICT (page_slug)
DO UPDATE SET
    view_count = page_views.view_count + 1,
    last_viewed = NOW();

-- Do nothing on conflict (idempotent insert)
INSERT INTO events (event_id, event_type, payload)
VALUES ('evt_001', 'signup', '{"email":"a@b.com"}')
ON CONFLICT (event_id)
DO NOTHING;   -- silently skip if event_id already exists

-- MySQL UPSERT: ON DUPLICATE KEY UPDATE
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', 1)
ON DUPLICATE KEY UPDATE
    theme         = VALUES(theme),
    notifications = VALUES(notifications),
    updated_at    = NOW();
-- VALUES(col) refers to the value from the INSERT clause

-- MySQL 8.0.20+ alias syntax (VALUES() is deprecated in newer versions):
INSERT INTO user_settings (user_id, theme)
VALUES (42, 'dark') AS new_row
ON DUPLICATE KEY UPDATE
    theme = new_row.theme;

-- Increment counter with MySQL UPSERT
INSERT INTO page_views (page_slug, view_count)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE
    view_count  = view_count + 1,
    last_viewed = NOW();

-- SQLite: INSERT OR REPLACE (deletes the old row, inserts a new one)
-- ⚠️ This resets ALL columns and auto-increment ID — use with care
INSERT OR REPLACE INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', 1);

-- SQLite 3.24+: supports PostgreSQL-style ON CONFLICT syntax
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', 1)
ON CONFLICT (user_id)
DO UPDATE SET
    theme         = excluded.theme,
    notifications = excluded.notifications;

-- SQLite: INSERT OR IGNORE (like ON CONFLICT DO NOTHING)
INSERT OR IGNORE INTO events (event_id, payload)
VALUES ('evt_001', '{}');

-- Sync product catalogue from an external feed (PostgreSQL)
INSERT INTO products (sku, name, price, stock, updated_at)
SELECT sku, name, price, stock, NOW()
FROM   staging_products           -- temporary staging table
ON CONFLICT (sku)
DO UPDATE SET
    name       = EXCLUDED.name,
    price      = EXCLUDED.price,
    stock      = EXCLUDED.stock,
    updated_at = EXCLUDED.updated_at
WHERE  products.price   != EXCLUDED.price
    OR products.stock   != EXCLUDED.stock
    OR products.name    != EXCLUDED.name;
-- The WHERE clause makes it a no-op if nothing changed (saves write I/O)