CPSC340A - Day 9 Notes

Everything prior to today was to read information from a database
Today we will modify values already in a database
Next class we will create new data or delte existing data
Change the value in a table using UPDATE statement
```
UPDATE TableName SET number = 0;
```
Note, this changed every row
When working with the example databases can reset the change by dropping the table then re-loading it
```
DROP TABLE TableName;
source database.sql;
```
But you typically won’t have a script that loads your entire database
Also if the database is large, this is slow

Can also restore the database state using transactions

START TRANSACTION;
UPDATE number SET number = 0;
SELECT * FROM TableName;
ROLLBACK;
SELECT * FROM TableName;
COMMIT;

The SQL queries after START TRANSACTION do not affect the database until the COMMIT statement
The ROLLBACK statement returns the database to the state it was in when the START TRANSACTION statement was executed

To not change every column, use WHERE to filter the rows affected by the update

UPDATE TableName SET number = 0 WHERE id = 1;
UPDATE TableName SET number = 0 WHERE name = 'somebody';

Can also use expressions for the update value
```
UPDATE TableName SET number = number + 1;
```

Can even change multiple columns at once

UPDATE TableName SET id = id + 1, number = number - 1;

Can also use subqueries, in the where clause

UPDATE TableName1 SET number = -1 WHERE id = (SELECT MAX(number) FROM TableName2);

Note, it can’t be a subquery on the same table

UPDATE TableName SET number = 0 WHERE number = (SELECT MAX(number) FROM TableName); -- Error

Can also use subqueries in the set clause

UPDATE TableName1 SET number = (SELECT MAX(number) FROM TableName2) WHERE id = 1;

Again, it can’t be a subquery on the same table

UPDATE TableName SET number = (SELECT MAX(number) FROM TableName) WHERE id = 1; -- Error