SQL DELETE

-- Single row
DELETE FROM users WHERE id = 42;

-- Many rows
DELETE FROM sessions WHERE expires_at < NOW();

-- DELETE from a join (Postgres)
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'XX';

-- TRUNCATE - removes ALL rows, much faster than DELETE
TRUNCATE TABLE staging_data;
-- TRUNCATE bypasses triggers, resets sequences, and cannot be rolled back in some DBs.

-- The deadly bug: forgotten WHERE
DELETE FROM users;             -- empties the table
-- Always SELECT first; always BEGIN a transaction in a SQL shell.

-- Safe pattern - transaction + check
BEGIN;
DELETE FROM users WHERE created_at < '2020-01-01';
-- did that delete the count you expected?
ROLLBACK;     -- or COMMIT once you are sure

-- Foreign keys + ON DELETE
CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  total   INT
);
-- Now DELETE FROM users CASCADES to orders

-- ON DELETE SET NULL keeps the child row
CREATE TABLE comments (
  id        SERIAL,
  author_id INT REFERENCES users(id) ON DELETE SET NULL,
  body      TEXT
);

-- ON DELETE RESTRICT (default) prevents deleting if children exist

-- Soft delete in production - common pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- Pretend the row is gone, but keep it for audit / undo
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- All queries then filter:
SELECT * FROM users WHERE deleted_at IS NULL;

-- Or a view
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- Hard delete for compliance (GDPR right to erasure)
-- Use after a retention period; document why and when.

DELETE FROM customers
WHERE id = 42;