INSERT, UPDATE and DELETE

Adding rows with INSERT

INSERT INTO is followed by the table name, the list of columns, and VALUES with the data in the same order. Naming the columns is safer than relying on the table's column order, because tables change over time.

INSERT INTO orders (id, customer, city, amount, status)
VALUES (7, 'Meena', 'Jaipur', 999, 'Placed');

-- many rows in one statement
INSERT INTO orders (id, customer, city, amount, status)
VALUES (8, 'Sunil', 'Indore', 450, 'Placed'),
       (9, 'Fatima', 'Lucknow', 1299, 'Placed');

Changing rows with UPDATE

UPDATE table SET column = new value WHERE condition. SET can change several columns, separated by commas, and the new value can use the old one, like amount = amount + 50.

UPDATE orders
SET status = 'Shipped'
WHERE id = 3;

Removing rows with DELETE

DELETE FROM orders
WHERE status = 'Cancelled';

The danger of forgetting WHERE

Seeing what changed: RETURNING

PostgreSQL and SQLite (since version 3.35) support RETURNING at the end of INSERT, UPDATE or DELETE. It gives back the changed rows, which is handy in an API: INSERT ... RETURNING id tells you the id of the new row. In the tasks below we use a separate SELECT after the change instead, so you can see the whole table.

INSERT INTO orders (customer, city, amount, status)
VALUES ('Ritu', 'Bhopal', 640, 'Placed')
RETURNING id;