SQL CRUD

Overview

CRUD operations mutate and retrieve table data. INSERT adds rows, UPDATE modifies existing rows, DELETE removes rows, and SELECT reads them. Use transactions when multiple statements must succeed or fail together.

Syntax / Usage

-- CREATE
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada Lovelace');

INSERT INTO tags (name)
SELECT unnest(ARRAY['js', 'sql', 'web']);

-- READ
SELECT * FROM users WHERE id = 'uuid-here';

-- UPDATE
UPDATE users
SET name = 'Ada L.', updated_at = NOW()
WHERE id = 'uuid-here';

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

-- Transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Examples

Upsert (Postgres):

INSERT INTO settings (user_id, theme)
VALUES ('abc', 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme;

Soft delete pattern:

UPDATE users
SET deleted_at = NOW()
WHERE id = $1 AND deleted_at IS NULL;

Common Mistakes

• UPDATE/DELETE without WHERE affecting every row
• Not using transactions for related multi-table changes
• Returning sensitive columns after insert without column lists
• Hard deletes when soft deletes are required for audit trails

See Also

select where-clause joins indexes