🎓 A1Learner
🔍

SQL Update

UPDATE is the SQL command that changes values in rows already stored. Its WHERE clause picks the rows to change.

Change one row

The WHERE clause names one product by its id. Only that row takes the new price.

UPDATE products
SET price = 44.99
WHERE product_id = 3;
SELECT name, price
FROM products
WHERE product_id = 3;

Change several rows

Two customers live in Austin. The WHERE clause matches both rows, so both rows move to Houston.

UPDATE customers
SET city = 'Houston'
WHERE city = 'Austin';
SELECT name, city
FROM customers
WHERE city = 'Houston'
ORDER BY name;

Change two columns

The SET clause here names price and stock. Both values change in the same row.

UPDATE products
SET price = 54.99, stock = 50
WHERE name = 'Headphones';
SELECT name, price, stock
FROM products
WHERE name = 'Headphones';

Example

UPDATE customers
SET city = 'Portland', country = 'USA'
WHERE name = 'Chen Wei';
SELECT name, city, country
FROM customers
WHERE name = 'Chen Wei';

Key points

  • UPDATE: the command that changes stored rows in place
  • SET: the keyword that names a column and its new value
  • One SET clause can change several columns, split by commas.
  • WHERE picks the rows an UPDATE changes.
  • UPDATE without WHERE changes every row in the table.
  • A closing SELECT proves the new values are stored.

In this example, the UPDATE statement targets the row for Chen Wei. The SET clause changes city to Portland and country to USA in that one row. The SELECT reads the row back. Both new values are stored, and no other row was named.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What happens when an UPDATE statement has no WHERE clause?