CPSC340A - Day 10 Notes

Review Update

Change the value in a table using UPDATE

UPDATE useless SET number = 0 WHERE id = 1;
But this only changes existing values
To create or destroy values need Delete and Insert

INSERT

Add new rows with insert

INSERT INTO TableName (id, name, number) VALUES (13, 'me', 3);

Using this syntax the order doesn’t matter

As long as the parenthesized lists are in the same order

INSERT INTO TableName (id, number, name) VALUES (14, 4, 'you');

It is possible to omit the list of columns, but hurts readability and rosbustness
```
INSERT INTO TableName VALUES (13, 'me', 3);
```
It is also possible to omit columns
But because every row must have the same number of cells

Unspecified cells are filled with default values

INSERT INTO TableName (id, number) VALUES (15, 3);

To prevent duplicating primary key id values, use subquery

INSERT INTO TableName (id, name, number) VALUES ((SELECT MAX(id) + 1 FROM TableName), 'you', 5);

Fix this by using a second subquery

INSERT INTO TableName (id, name, number) VALUES ((SELECT MAX(id) + 1 FROM (Select * from TableName) AS NotTableName), 'you', 3);

Delete

If you want to delete, have to delete an entire row
Delete syntax is similar to select, just don’t specify the column
```
DELETE FROM TableName WHERE name = 'you';
```
Deleting a single cell is not possible as the table must be a rectanglar grid
Every row must have the same number of cells
And every column must have the same number of cells
To delete all rows, just omit the where
```
DELETE FROM TableName;
```

Can also use subqueries

DELETE FROM Table1 WHERE id in (SELECT number FROM Table2);

Just like insert, the table in the subquery can not be the same as the table being deleted from

Archiving

Using delete and insert together to archive data

CREATE TABLE TableName_archive (id INT, name TEXT, number INT);
INSERT INTO TableName_archive (id, name, number) SELECT id, name, number FROM TableName WHERE name = 'me';
DELETE FROM TableName WHERE name = 'me';

Note that VALUES is not needed for the insert