Modifying Data

Insert, update, and delete rows safely and efficiently.

INSERT

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

-- Multiple rows
INSERT INTO users (name, email)
VALUES ('Lin', 'lin@example.com'), ('Ken', 'ken@example.com');

UPDATE

UPDATE users
SET    email = 'ada@new.example'
WHERE  id = 1;

Always include a WHERE unless you intend to change every row.

DELETE

DELETE FROM users WHERE id = 1;

RETURNING (engine-specific)

Return affected rows directly (Postgres, some others):

UPDATE users SET name = 'Ada L.' WHERE id = 2 RETURNING id, name;
INSERT INTO users (name) VALUES ('Neo') RETURNING id;

Upserts (INSERT or UPDATE)

Postgres:

INSERT INTO users (id, email)
VALUES (42, 'x@example.com')
ON CONFLICT (id)
DO UPDATE SET email = EXCLUDED.email;

MySQL/MariaDB:

INSERT INTO users (id, email)
VALUES (42, 'x@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

Modifying with joins/CTEs

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) AS rn FROM users
)
UPDATE users u
SET    rank = r.rn
FROM   ranked r
WHERE  u.id = r.id;

Safety and performance

Always test WHERE with a SELECT before UPDATE/DELETE
Use transactions for multi-step changes
Prefer batches for large inserts; disable triggers/constraints only when necessary and safe

Summary

Use INSERT/UPDATE/DELETE with precise filters
Leverage RETURNING and upserts where supported for concise, safe workflows