INSERT, UPDATE, DELETE

-- INSERT: Add new rows
INSERT INTO users (name, email, role)
VALUES ('Alice', 'alice@test.com', 'admin');

-- Multiple rows at once
INSERT INTO products (name, price, category) VALUES
    ('Widget A', 29.99, 'electronics'),
    ('Widget B', 39.99, 'electronics'),
    ('Gadget C', 19.99, 'accessories');

-- INSERT from SELECT (copy data)
INSERT INTO archived_orders (id, customer_id, total, created_at)
SELECT id, customer_id, total, created_at
FROM orders WHERE created_at < '2024-01-01';

-- UPDATE: Modify existing rows
UPDATE users
SET role = 'editor', updated_at = NOW()
WHERE id = 42;

-- ⚠️ NEVER run UPDATE without WHERE! This updates ALL rows:
-- UPDATE users SET role = 'viewer';  -- DANGER!

-- UPDATE with JOIN (MySQL)
UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.category_name = c.name
WHERE c.active = 1;

-- UPSERT: Insert or update if exists
INSERT INTO settings (user_id, key, value)
VALUES (1, 'theme', 'dark')
ON DUPLICATE KEY UPDATE value = 'dark';
-- PostgreSQL: ON CONFLICT (user_id, key) DO UPDATE SET value = 'dark'

-- DELETE: Remove rows
DELETE FROM sessions
WHERE last_active < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- TRUNCATE: Delete all rows (faster than DELETE, no rollback)
TRUNCATE TABLE temp_data;

-- Safe delete pattern: check first, then delete
SELECT COUNT(*) FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01';
-- Verify the count, then:
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01';