TopolearnReading data is half the job; applications constantly create, change and remove rows.
INSERT adds rows. Always list the target columns, so the statement does not depend on column order and omitted columns get their defaults:
INSERT INTO customers (name, email, city)
VALUES ('Eve', 'eve@example.com', 'Paris'),
('Finn', 'finn@example.com', NULL);One statement with several VALUES rows is much faster than many single-row inserts. INSERT INTO t (...) SELECT ... copies the result of a query.
UPDATE changes columns of the rows that match WHERE. The right-hand side can use the old values:
UPDATE products SET price = price * 1.10 WHERE category = 'hardware';DELETE removes matching rows:
DELETE FROM orders WHERE status = 'cancelled';The rule that saves careers: an UPDATE or DELETE without WHERE touches every row. Run the equivalent SELECT first, and when working by hand, do it inside a transaction (BEGIN; ... check the row count ... COMMIT; or ROLLBACK;).
RETURNING (PostgreSQL, SQLite and MariaDB; SQL Server's equivalent is OUTPUT) hands back the affected rows, so you can fetch a generated id or the new values without a second query. MySQL has no RETURNING; use LAST_INSERT_ID() there.
To empty a whole table, TRUNCATE orders is much faster than DELETE FROM orders because it does not visit rows one by one. It needs a stronger lock and does not fire row-level triggers, and in PostgreSQL it is still transactional.
Every statement reports how many rows it affected (UPDATE 3 in psql). Check that number in application code; an update that matched zero rows is often a bug.
Example
INSERT INTO customers (name, email, city)
VALUES ('Eve', 'eve@example.com', 'Paris')
RETURNING id, name;
-- id | name
-- 5 | Eve
UPDATE products
SET price = price * 1.10
WHERE category = 'hardware'
RETURNING name, price;
-- Keyboard | 53.90
-- Mouse | 21.45
-- Monitor | 207.90
BEGIN;
DELETE FROM orders WHERE status = 'cancelled'; -- DELETE 1
-- looks right? COMMIT; otherwise:
ROLLBACK;Remember
- Always list the columns in INSERT
- UPDATE/DELETE without WHERE affects every row
- RETURNING gives you generated ids and new values in the same round trip
- TRUNCATE empties a table far faster than DELETE
Watch out for
- Running an UPDATE whose WHERE clause was accidentally not selected in the SQL editor.