Lesson 8.3

Updating Rows with SQL UPDATE

UPDATE changes rows in place. The WHERE clause decides which ones. Leave it off and you change them all.

UPDATE changes the values in rows that already exist. You can set several columns in one statement by separating them with commas.

The most important part of an UPDATE is the WHERE. Without it, the change applies to every row in the table. That is the accident this chapter is named after, so write the filter first, and confirm with a SELECT afterward.

Pattern

update products
set price = 19.99
where product_id = 4;

select product_id, price from products where product_id = 4;

Syntax reference

Need syntax details? See the safe UPDATE and DELETE reference

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Order 7 just shipped. Update it: set its status to 'shipped' and its shipped_date to 2024-06-20, touching only order 7. Then confirm that it changed and nothing else did: select order_id, status, and shipped_date for order 7 and for every order that is still not shipped, sorted by order_id.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: order_id, status, shipped_date.
Rows: 7, order 7 plus the six orders that are still not shipped, by order_id.
Order 7 now reads shipped on 2024-06-20; orders 4, 9, 13, 17, 19, and 22 are unchanged.

Previous · 8.2 Adding rows with INSERT

Next · 8.4 Deleting rows safely →

Reference

Safe SQL UPDATE and DELETE

A checklist for changing the right rows.

Cheatsheet

SQL CASE Expressions Cheatsheet

Searched and simple CASE, plus conditional aggregation.

Lesson

What Is SQL DML?

Understand INSERT, UPDATE, and DELETE.

Lesson