Level 4 of 13 · Changing your mind

SQL UPDATE Statement for Beginners: Examples and Practice

Changing rows that are already there with UPDATE and SET, and the WHERE clause that stops you changing every row by accident.

Covers: UPDATE · SET · WHERE · several columns · values from the old value

Practise UPDATE

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# Change rows: UPDATE UPDATE changes values in rows that are already in a table. You need it whenever data changes: a price goes up, a customer moves, an order is delivered. - `UPDATE pizzas` which table - `SET price = 9` which column, and its new value - `WHERE id = 1` which rows > Update by the primary key whenever you can. WHERE id = 1 can only ever touch one row.

UPDATE pizzas
SET price = 9
WHERE id = 1;
SELECT * FROM pizzas WHERE id = 1

# Change several columns at once - `SET city = 'York', nickname = 'Bobby'` separate the changes with commas > Write SET a = 1, b = 2, never SET a = 1 AND b = 2. With AND, MySQL quietly sets a to the result of a comparison, 0 or 1.

UPDATE customers
SET city = 'York',
  nickname = 'Bobby'
WHERE id = 2;
SELECT * FROM customers WHERE id = 2

# Use the old value - `price * 1.1` the old price plus 10% - `ROUND(…, 2)` rounded to 2 decimal places There is no WHERE, so every row changes. > Here changing every row is what we want. Most of the time it isn't: see the next slide.

UPDATE pizzas
SET price = ROUND(price * 1.1, 2);
SELECT name, price FROM pizzas

# Forgetting WHERE UPDATE without WHERE changes every row in the table. Now every customer lives in Leeds. > Write the WHERE before the SET. Many teams turn on MySQL's safe updates mode, which refuses an UPDATE whose WHERE doesn't use a key.

UPDATE customers SET city = 'Leeds';
SELECT name, city FROM customers

# SELECT before you UPDATE Run the WHERE as a SELECT first. If it returns exactly the rows you mean, change SELECT * FROM to UPDATE … SET and keep the same WHERE. > On real data, also run it inside a transaction so you can undo a mistake. That's in the Errors & Debugging level.

SELECT *
FROM orders
WHERE status = 'on the way'

Appian corner

In Appian you rarely write UPDATE yourself: Write Records (the smart service, or a!writeRecords) updates a row when the record you pass has its primary key, and inserts one when it does not. You write UPDATE when you fix data directly in the database, and always with a WHERE.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Mark order 8 as delivered.

UPDATE orders
___ status = 'delivered'
WHERE id = ___

Show the answer

UPDATE orders
SET status = 'delivered'
WHERE id = 8

The keyword before the column, and the order number.

2. What does this do?
```
UPDATE customers
SET nickname = 'Pal'
```
- Only customers without a nickname become 'Pal'
- Every customer's nickname becomes 'Pal'
- Nothing, it needs a WHERE
- Only the first customer changes
Show the answer
Every customer's nickname becomes 'Pal'. No WHERE means every row. The database will not stop you.
3. Bob (customer 2) moved. Set his city to 'York'.
Show the answer
```
UPDATE customers
SET city = 'York'
WHERE id = 2
```
SET city = 'York' WHERE id = 2
4. The Diavola (pizza 5) is going veggie and losing its heat: set veg to 1 and spicy to 0, in one statement.
Show the answer
```
UPDATE pizzas
SET veg = 1, spicy = 0
WHERE id = 5
```
One SET, the two columns separated by a comma, and WHERE id = 5.
5. Every order with no tip (NULL) should have a tip of 0.
Show the answer
```
UPDATE orders
SET tip = 0
WHERE tip IS NULL
```
WHERE tip IS NULL. Remember, = NULL never matches.
6. This was meant to make only the Margherita (id 1) cost 1 more, but it changes every pizza. Fix it.
```
UPDATE pizzas SET price = price + 1
```
Show the answer
```
UPDATE pizzas
SET price = price + 1
WHERE id = 1
```
It is missing a WHERE.
7. Boss question! Every spicy pizza (spicy = 1) costs 10% more, rounded to 2 decimal places.
Show the answer
```
UPDATE pizzas
SET price = ROUND(price * 1.1, 2)
WHERE spicy = 1
```
SET price = ROUND(price * 1.1, 2) WHERE spicy = 1

Practise UPDATE