SQL › Fixing and Removing Rows

UPDATE, DELETE and TRUNCATE

UPDATE changes values in rows that already exist, DELETE removes rows, and TRUNCATE empties a whole table at once.

Syntax

Order of execution: FROM (and any other table) → WHERE → SET or delete → RETURNING

WHERE picks the rows before anything changes. Leave it out and every row in the table is changed or deleted.

Code
UPDATE table_name
SET column1 = expression, column2 = expression
FROM other_table               -- optional
WHERE condition
RETURNING column1, ...;
 
DELETE FROM table_name
WHERE condition
RETURNING column1, ...;
 
TRUNCATE table_name [RESTART IDENTITY];

Examples

Check first

Make sure you have the right rows before you change them.

Code
-- 1. See the rows the WHERE picks
SELECT product_id, product_name, stock FROM products WHERE product_id = 4;
 
-- 2. Same WHERE, now change them
UPDATE products SET stock = 25 WHERE product_id = 4;

Update with expressions

Change values based on what is already there, in one or more columns.

Code
-- Toys: 10% off and 20 more in stock
UPDATE products
SET price = price * 0.9, stock = stock + 20
WHERE category_id = 8
RETURNING product_name, price, stock;

Update from another table

Pick the rows to change using values stored in a different table.

Code
-- Mark every product in the 'Smart Home' category unavailable
UPDATE products
SET is_available = false
FROM categories
WHERE categories.category_id = products.category_id
  AND categories.category_name = 'Smart Home'
RETURNING products.product_name, products.is_available;

Delete rows

Remove only the rows you no longer need.

Code
-- Remove the low-rated reviews
DELETE FROM reviews
WHERE rating <= 2
RETURNING review_id, rating;
 
-- Fails: orders still point to customer 1
DELETE FROM customers WHERE customer_id = 1;

Empty a table

Remove every row of a table at once.

Code
-- DELETE without WHERE removes every row, but the id counter keeps going
DELETE FROM accounts;
 
-- TRUNCATE is faster; RESTART IDENTITY also sets the next id back to 1
TRUNCATE accounts RESTART IDENTITY;

DELETE, TRUNCATE or DROP

All three get rid of data, but they remove different things.

TRUNCATE is refused while another table's foreign key points to the table; PostgreSQL accepts TRUNCATE … CASCADE, which empties those tables too.

Remember

• No WHERE means every row. Run the WHERE as a SELECT first.
• AND is checked before OR: use brackets or IN when you mix them.
• UPDATE ... FROM changes each row once, even when it matches several rows of the other table.
• DELETE keeps the id counter going; PostgreSQL's TRUNCATE ... RESTART IDENTITY resets it.
• A foreign key can refuse a DELETE while other rows still point to the row.

Practice

1. Clear Failed UPI and Card Payments (Easy)
2. Reward Customers Who Reviewed (Easy)
3. Start the Reviews Over (Medium)