AI Hints
Homeโ€บSQL Tutorialโ€บCreating and Changing Data
SQL ยท Lesson 73 of 126

Deleting Rows

DELETE FROM, safely.

โฑ 2 min readโ–ถ Video at 1:42:40๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen wants to clear out old cancelled orders from the first half of twenty twenty four. Deleting is permanent. There's no undo button. So we'll do it the careful way. Check first, then delete.

Deleting Rows
From the video: Deleting Rows
  • Remove old cancelled orders
  • Deleting is permanent
  • Check first, then delete

Step one: check

First, a select with the exact where we plan to use. How many rows will be deleted? Run it. Fifteen orders. That sounds right, so we can go ahead.

query.sqlsql
SELECT COUNT(*) AS to_delete
FROM orders
WHERE status = 'cancelled'
  AND order_date < '2024-07-01';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ to_delete โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 15        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Step two: delete

Now we swap select for delete from, and keep the same where. Run it. Fifteen fewer orders. There were one thousand five hundred and ninety eight, and now there are one thousand five hundred and eighty three.

query.sqlsql
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2024-07-01';

SELECT COUNT(*) AS orders_left
FROM orders;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ orders_left โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1583        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

A pay rise

Your turn. Helen is giving every bookseller a one thousand dollar pay rise. Write the update, then show the booksellers' new salaries. Pause and try. Here's a solution. Set salary to salary plus one thousand, where the role is bookseller.

โœ๏ธ Exercise: A pay rise

Give every Bookseller +1000 salary, then show them

Show hint

UPDATE staff SET salary = salary + 1000 WHERE ...

Show solution
solution.sqlsql
UPDATE staff
SET salary = salary + 1000
WHERE role = 'Bookseller';

SELECT name, salary
FROM staff
WHERE role = 'Bookseller';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    name    โ”‚ salary โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Tom Baker  โ”‚ 32000  โ”‚
โ”‚ Sara Odum  โ”‚ 33000  โ”‚
โ”‚ Noah Price โ”‚ 31500  โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Delete from, with a where, removes rows forever. Always check with a select first. Next, rules that stop bad data getting in.

Key takeaways
  • DELETE FROM t WHERE ...
  • Check with SELECT first
  • No WHERE = empty table
Next up: Constraints โ€” rules that protect your data