SQL UPDATE Tutorial
The UPDATE command enables you to change the data in a table.
This page shows how to use this command in PostgreSQL, MySQL, SQL Server, and Oracle.
Simple UPDATE
- Works in PostgreSQL, MySQL, SQL Server, and Oracle.
- Updates the
dollar_amount value in every row to 1000.
UPDATE rent_payments SET dollar_amount = 1000;
rent_payments table before
| date |
tenant_id |
dollar_amount |
| 3/1/2018 |
1 |
2000 |
| 3/2/2018 |
2 |
1500 |
| 3/1/2018 |
3 |
1800 |
| 3/3/2018 |
4 |
1900 |
rent_payments table after
| date |
tenant_id |
dollar_amount |
| 3/1/2018 |
1 |
1000 |
| 3/2/2018 |
2 |
1000 |
| 3/1/2018 |
3 |
1000 |
| 3/3/2018 |
4 |
1000 |
Multi column UPDATE
To update multiple columns, separate the columns you want to update with commas.
- Works in PostgreSQL, MySQL, SQL Server, and Oracle.
- Updates the
dollar_amount and tenant_id columns.
UPDATE
rent_payments
SET
dollar_amount = 1000,
tenant_id = 5;
rent_payments table before
| date |
tenant_id |
dollar_amount |
| 3/1/2018 |
1 |
2000 |
| 3/2/2018 |
2 |
1500 |
| 3/1/2018 |
3 |
1800 |
| 3/3/2018 |
4 |
1900 |
rent_payments table after
| date |
tenant_id |
dollar_amount |
| 3/1/2018 |
5 |
1000 |
| 3/2/2018 |
5 |
1000 |
| 3/1/2018 |
5 |
1000 |
| 3/3/2018 |
5 |
1000 |
UPDATE WHERE
- Works in PostgreSQL, MySQL, SQL Server, and Oracle.
- The following command updates
dollar_amount where tenant_id is equal to 1.
UPDATE
rent_payments
SET
dollar_amount = 1000
WHERE
tenant_id = 1;
rent_payments table before
| date |
tenant_id |
dollar_amount |
| 3/1/2018 |
1 |
2000 |
| 3/2/2018 |
2 |
1500 |
| 3/1/2018 |
3 |
1800 |
| 3/3/2018 |
4 |
1900 |
rent_payments table after
| date |
tenant_id |
dollar_amount |
| 3/1/2018 |
1 |
1000 |
| 3/2/2018 |
2 |
1500 |
| 3/1/2018 |
3 |
1800 |
| 3/3/2018 |
4 |
1900 |
UPDATE with CASE
- Works in PostgreSQL, MySQL, SQL Server, and Oracle.
- The
CASE keyword allows us to update data based on IF / ELSE logic.
- Here, we set the
cost_category column using the following rules:
- If
dollar_amount < 1600, we set cost_category to cheap.
- If
dollar_amount >= 2000, we set cost_category to expensive.
- Else, we set
cost_category to average.
UPDATE
rent_payments
SET cost_category = (
CASE
WHEN dollar_amount < 1600 THEN 'cheap'
WHEN dollar_amount >= 2000 THEN 'expensive'
ELSE 'average'
END
);
rent_payments table before
| payment_date |
dollar_amount |
cost_category |
| 3/1/2018 |
2000 |
|
| 3/2/2018 |
1500 |
|
| 3/1/2018 |
1800 |
|
| 3/3/2018 |
1900 |
|
rent_payments table after
| payment_date |
dollar_amount |
cost_category |
| 3/1/2018 |
2000 |
expensive |
| 3/2/2018 |
1500 |
cheap |
| 3/1/2018 |
1800 |
average |
| 3/3/2018 |
1900 |
average |
