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

Changing Rows

UPDATE ... SET.

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

Helen's supplier has raised prices on technology books. She needs to put them up by ten percent. And she wants to replace the missing cities with the word unknown. Both of these change existing rows. That's update.

Changing Rows
From the video: Changing Rows
  • Tech books up by 10%
  • Fill in missing cities
  • Change existing rows

UPDATE ... SET

Update, the table, then set, the column, and its new value. The new value can use the old one. Price times one point one, rounded. And where says which rows to change. Run it. All three technology books are now ten percent more expensive.

query.sqlsql
UPDATE books
SET price = ROUND(price * 1.10, 2)
WHERE genre = 'Technology';

SELECT title, price
FROM books
WHERE genre = 'Technology';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Python for Curious Minds โ”‚ 38.49 โ”‚
โ”‚ SQL in a Weekend         โ”‚ 32.99 โ”‚
โ”‚ Data for Everyone        โ”‚ 35.19 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Fill in the blanks

Here we replace every null city with the word unknown. Run it. Fifteen customers had no city. Now they all say unknown.

query.sqlsql
UPDATE customers
SET city = 'Unknown'
WHERE city IS NULL;

SELECT COUNT(*) AS unknown_city
FROM customers
WHERE city = 'Unknown';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ unknown_city โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 15           โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The most dangerous mistake

Here's the most important warning in this course. If you forget the where, update changes every single row in the table. Every book would get the new price. So always write the where first. Even better, run a select with the same where, to see which rows you're about to change.

No WHEREevery row changes
Write WHERE firstthen the SET
Check firstSELECT with the same WHERE
Thenrun the UPDATE

Summary

Update, set, and where change existing rows. Never forget the where. Next, deleting rows.

Key takeaways
  • UPDATE t SET col = value
  • WHERE picks the rows
  • No WHERE = every row
Next up: Deleting Rows โ€” DELETE FROM, safely