Bits of .NET

🔄 One Query: Insert or Update

Check if exists → Update or Insert = 2 queries. UPSERT does it in one. Faster, atomic, race-condition free.

📝 PostgreSQL (ON CONFLICT)

-- Basic UPSERT (update if conflict)
INSERT INTO users (id, name, email, last_login)
VALUES (1, 'Alice', 'alice@example.com', NOW())
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name,
    email = EXCLUDED.email,
    last_login = EXCLUDED.last_login;

-- UPSERT with specific columns
INSERT INTO products (sku, name, price, stock)
VALUES ('ABC-123', 'Widget', 19.99, 100)
ON CONFLICT (sku) DO UPDATE 
SET price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;  -- Increment stock

-- WHERE clause in UPSERT
INSERT INTO inventory (product_id, warehouse_id, quantity)
VALUES (1, 5, 50)
ON CONFLICT (product_id, warehouse_id) DO UPDATE 
SET quantity = EXCLUDED.quantity
WHERE inventory.quantity < 100;  -- Only update if less than 100

-- DO NOTHING (ignore if exists)
INSERT INTO logs (event_id, message)
VALUES (123, 'User login')
ON CONFLICT (event_id) DO NOTHING;

🎯 MySQL (ON DUPLICATE KEY)

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email);

-- Increment counter
INSERT INTO page_views (page_url, view_count)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE 
    view_count = view_count + 1;

✅ Use Cases

-- Daily stats aggregation
INSERT INTO daily_stats (stat_date, user_count, revenue)
VALUES (CURRENT_DATE, 1, 99.99)
ON CONFLICT (stat_date) DO UPDATE 
SET user_count = daily_stats.user_count + 1,
    revenue = daily_stats.revenue + EXCLUDED.revenue;

-- Session tracking
INSERT INTO user_sessions (session_id, user_id, last_active)
VALUES ('abc123', 42, NOW())
ON CONFLICT (session_id) DO UPDATE 
SET last_active = EXCLUDED.last_active;

-- Batch import (ignore duplicates)
INSERT INTO products (sku, name, price)
VALUES 
    ('A001', 'Product 1', 10.00),
    ('A002', 'Product 2', 20.00)
ON CONFLICT (sku) DO NOTHING;

💡 Database Support

• PostgreSQL: ON CONFLICT (most flexible)
• MySQL: ON DUPLICATE KEY (simpler)
• SQLite: ON CONFLICT (similar to PostgreSQL)
• SQL Server: MERGE (more verbose)