Modifying Data

-- Single row, all columns (order matches table definition)
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com', NOW());

-- Single row, named columns (recommended — order-independent)
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- Multiple rows in one statement
INSERT INTO products (name, price, category)
VALUES
    ('Widget', 9.99,  'Gadgets'),
    ('Gizmo',  14.99, 'Gadgets'),
    ('Doohickey', 4.99, 'Parts');

-- Insert from SELECT
INSERT INTO archive_orders (id, user_id, total, archived_at)
SELECT id, user_id, total, NOW()
FROM orders
WHERE created_at < '2023-01-01';

-- Insert with default values
INSERT INTO logs DEFAULT VALUES;
INSERT INTO logs (message) VALUES ('event');    -- other columns use defaults

-- INSERT RETURNING (PostgreSQL) — get values from the inserted row
INSERT INTO users (name, email)
VALUES ('Bob', 'bob@example.com')
RETURNING id, created_at;

-- OUTPUT (SQL Server equivalent)
INSERT INTO users (name, email)
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('Bob', 'bob@example.com');

-- PostgreSQL: ON CONFLICT DO NOTHING (ignore duplicate)
INSERT INTO tags (name)
VALUES ('sql'), ('database')
ON CONFLICT (name) DO NOTHING;

-- PostgreSQL: ON CONFLICT DO UPDATE (upsert)
INSERT INTO users (id, email, updated_at)
VALUES (42, 'newemail@example.com', NOW())
ON CONFLICT (id) DO UPDATE
    SET email      = EXCLUDED.email,
        updated_at = EXCLUDED.updated_at;

-- ON CONFLICT with WHERE (partial upsert)
INSERT INTO products (sku, price)
VALUES ('ABC', 19.99)
ON CONFLICT (sku) DO UPDATE
    SET price = EXCLUDED.price
    WHERE products.price <> EXCLUDED.price;  -- only update if price changed

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO users (id, email, updated_at)
VALUES (42, 'newemail@example.com', NOW())
ON DUPLICATE KEY UPDATE
    email      = VALUES(email),
    updated_at = VALUES(updated_at);

-- MySQL: REPLACE INTO (delete + insert on conflict — careful with FKs)
REPLACE INTO users (id, name, email) VALUES (42, 'Bob', 'bob@example.com');

-- SQLite: INSERT OR REPLACE / INSERT OR IGNORE
INSERT OR IGNORE INTO tags (name) VALUES ('sql');
INSERT OR REPLACE INTO users (id, name) VALUES (42, 'Alice');

-- SQL Server: MERGE (upsert)
MERGE users AS target
USING (VALUES (42, 'bob@example.com')) AS source(id, email)
ON target.id = source.id
WHEN MATCHED     THEN UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (source.id, source.email);

-- Update all rows (dangerous — almost always add a WHERE)
UPDATE products SET stock = 0;

-- Update specific rows
UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '1 year';

-- Update multiple columns
UPDATE employees
SET salary    = salary * 1.10,
    title     = 'Senior Engineer',
    updated_at = NOW()
WHERE id = 123;

-- Update with expression
UPDATE orders SET total = subtotal + tax WHERE total IS NULL;

-- Update using another table (correlated subquery)
UPDATE employees e
SET salary = (
    SELECT AVG(salary) FROM employees WHERE department = e.department
)
WHERE title = 'Intern';

-- Update with JOIN (PostgreSQL FROM clause)
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id
  AND u.status  = 'banned';

-- Update with JOIN (MySQL)
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.status = 'cancelled'
WHERE u.status = 'banned';

-- RETURNING (PostgreSQL) — get affected rows back
UPDATE users SET status = 'active'
WHERE email_verified = TRUE
RETURNING id, email;

-- Limit updates (MySQL)
UPDATE products SET stock = 0 WHERE stock < 0 LIMIT 100;

-- Delete all rows (use TRUNCATE for full table wipe)
DELETE FROM session_tokens;

-- Delete specific rows
DELETE FROM users WHERE status = 'deleted' AND updated_at < NOW() - INTERVAL '30 days';

-- Delete with subquery
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

-- Delete with JOIN (MySQL)
DELETE oi
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'cancelled';

-- Delete with USING (PostgreSQL)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled';

-- RETURNING (PostgreSQL) — return deleted rows
DELETE FROM sessions WHERE expires_at < NOW()
RETURNING id, user_id;

-- Delete with CTE (PostgreSQL)
WITH expired AS (
    SELECT id FROM sessions WHERE expires_at < NOW()
)
DELETE FROM sessions WHERE id IN (SELECT id FROM expired);

-- Limit deletes (MySQL)
DELETE FROM logs WHERE level = 'debug' ORDER BY created_at LIMIT 1000;

-- Truncate a table
TRUNCATE TABLE session_tokens;

-- Truncate and reset identity/serial counter
TRUNCATE TABLE orders RESTART IDENTITY;

-- Truncate multiple tables
TRUNCATE TABLE cart_items, carts;

-- Cascade truncate (also truncate referencing tables)
TRUNCATE TABLE users CASCADE;

-- TRUNCATE vs DELETE:
-- TRUNCATE: DDL in some DBs (not transactional in MySQL), resets sequences,
--           ignores ON DELETE triggers, cannot filter rows.
-- DELETE:   DML, fully transactional, fires triggers, can use WHERE, RETURNING.

-- SQL Standard MERGE (PostgreSQL 15+, SQL Server, Oracle)
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id

WHEN MATCHED AND source.deleted = TRUE
    THEN DELETE

WHEN MATCHED
    THEN UPDATE SET
        target.name       = source.name,
        target.updated_at = NOW()

WHEN NOT MATCHED
    THEN INSERT (id, name, created_at)
         VALUES (source.id, source.name, NOW());

-- MERGE with a values list as source
MERGE INTO products AS t
USING (VALUES (101, 'Widget', 9.99)) AS s(id, name, price)
ON t.id = s.id
WHEN MATCHED     THEN UPDATE SET t.price = s.price
WHEN NOT MATCHED THEN INSERT (id, name, price) VALUES (s.id, s.name, s.price);

-- PostgreSQL RETURNING
INSERT INTO orders (user_id, total) VALUES (1, 99.99) RETURNING id, created_at;
UPDATE users SET status = 'active' WHERE id = 42 RETURNING *;
DELETE FROM sessions WHERE user_id = 42 RETURNING id;

-- SQL Server OUTPUT (into a temp table or variable)
INSERT INTO orders (user_id, total)
OUTPUT INSERTED.id, INSERTED.created_at INTO @new_orders
VALUES (1, 99.99);

UPDATE users SET status = 'active'
OUTPUT DELETED.status AS old_status, INSERTED.status AS new_status
WHERE id = 42;

-- COPY (PostgreSQL) — fastest bulk import from file
COPY users (id, name, email) FROM '/tmp/users.csv' CSV HEADER;
COPY users TO '/tmp/users.csv' CSV HEADER;

-- COPY from stdin (psql)
COPY products FROM STDIN WITH (FORMAT csv, HEADER true);

-- MySQL: LOAD DATA INFILE
LOAD DATA INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Batch inserts: combine multiple rows in one INSERT
-- Much faster than one INSERT per row

-- Disable indexes for bulk loads (MySQL)
ALTER TABLE products DISABLE KEYS;
-- ... bulk insert ...
ALTER TABLE products ENABLE KEYS;

-- Deferred constraint checking (PostgreSQL)
SET CONSTRAINTS ALL DEFERRED;
-- ... insert rows that temporarily violate FK constraints ...
COMMIT;  -- constraints checked at commit time

-- UNION: combine and deduplicate
SELECT id FROM customers
UNION
SELECT id FROM leads;

-- UNION ALL: combine without deduplication (faster)
SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'lead' AS type FROM leads;

-- INTERSECT: rows that appear in both
SELECT user_id FROM premium_users
INTERSECT
SELECT user_id FROM active_users;

-- EXCEPT / MINUS: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;    -- users who never ordered

-- Rules:
-- Same number of columns in all SELECT statements
-- Compatible data types per column position
-- Column names from first SELECT are used
-- ORDER BY applies to the final result
SELECT a, b FROM t1
UNION ALL
SELECT c, d FROM t2
ORDER BY 1 DESC;