Performing Atomic Upsert (Insert or Update) Operations in SQL

-- PostgreSQL Syntax (ON CONFLICT)
-- Assumes 'product_sku' is a UNIQUE constraint.
INSERT INTO products (product_sku, name, price, stock)
VALUES ('SKU001', 'Widget A', 29.99, 100)
ON CONFLICT (product_sku) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock; -- Example: increment stock

-- MySQL Syntax (ON DUPLICATE KEY UPDATE)
-- Assumes 'product_sku' is a UNIQUE or PRIMARY KEY constraint.
INSERT INTO products (product_sku, name, price, stock)
VALUES ('SKU002', 'Gadget B', 49.99, 50)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price),
    stock = stock + VALUES(stock); -- Example: increment stock

-- SQL Server (MERGE statement)
-- Requires a temporary source or subquery
MERGE INTO products AS target
USING (VALUES ('SKU003', 'Doodad C', 19.99, 200)) AS source (product_sku, name, price, stock)
ON target.product_sku = source.product_sku
WHEN MATCHED THEN
    UPDATE SET
        name = source.name,
        price = source.price,
        stock = target.stock + source.stock
WHEN NOT MATCHED THEN
    INSERT (product_sku, name, price, stock)
    VALUES (source.product_sku, source.name, source.price, source.stock);