INSERT, UPDATE, DELETE

Every statement you have written so far only read rows. These three change what is stored: INSERT adds rows, UPDATE rewrites columns in rows that already exist, and DELETE removes rows. Together with SELECT they are called DML - Data Manipulation Language, the statements that work on the data inside a table rather than on the table's definition.

A script runs top to bottom against one database, so a SELECT reports whatever the statements above it left behind. Change something after the SELECT and the printed rows will not show it.

INSERT adds rows

CREATE TABLE tickets (code TEXT, price INTEGER);
INSERT INTO tickets (code, price) VALUES ('A1', 20);
INSERT INTO tickets VALUES ('B2', 35);
INSERT INTO tickets (code, price) VALUES ('C3', 15), ('D4', 50);
SELECT code, price FROM tickets ORDER BY code;

A1|20
B2|35
C3|15
D4|50

Three forms, one effect. With a column list, each value goes to the column you named. Without a column list, values are matched by position - the order the columns were given in CREATE TABLE. One VALUES clause may carry several parenthesised rows separated by commas, which is one statement instead of many.

Position matching is silent when you get it wrong. INSERT INTO tickets VALUES (50, 'E5'); stores 50 in code and the text E5 in price; SQLite accepts it and the row prints as 50|E5.

UPDATE rewrites columns of rows you choose

UPDATE tickets SET price = price + 5 WHERE code = 'A1';

SET names a column and its new value. The right-hand side may read the row being changed, so price + 5 means "five more than this row's current price". WHERE picks which rows are touched, exactly as in a SELECT. Starting from the four rows above, the table now holds:

A1|25
B2|35
C3|15
D4|50

Leave the WHERE off and the statement rewrites every row instead. From the same four starting rows:

UPDATE tickets SET price = price + 5;

A1|25
B2|40
C3|20
D4|55

Nothing warns you. The statement is legal; it just did four times the work you meant.

DELETE removes whole rows

DELETE FROM tickets WHERE price < 30;

B2|35
D4|50

DELETE takes no column list - a row goes away entirely, or it stays. DELETE FROM tickets; with no WHERE empties the table, and a SELECT after it prints nothing at all: not a zero, not a blank line, no output.

Quotes decide what a word means

'A1' in single quotes is a text value. A1 bare is a column name, and a table with no such column gives:

UPDATE tickets SET price = 99 WHERE code = A1;

Error: near line 3: no such column: A1

The failing statement changes nothing, the rest of the script still runs and still prints rows, and the submission is marked as an error anyway. Text always needs single quotes; numbers never do.

Common mistakes

• UPDATE without WHERE - every row takes the new value, so unrelated people end up on the same salary instead of only the one you meant.
• DELETE without WHERE - the table is emptied and the final SELECT prints nothing, so the output is blank rather than short by one row.
• Bare text in a comparison - WHERE code = A1 is read as a column name and fails with no such column: A1; the row you wanted is left untouched.
• Positional INSERT with the values swapped - the columns are filled in CREATE TABLE order, so a reversed pair prints as 50|E5 instead of E5|50.

Your exercise

Bring the employees table up to date with three changes and then report what is left. The starter already creates the table and fills it with four rows, so you only add statements under the TODO comments. The mistake to watch for is a missing WHERE: without it your update or your delete hits every employee in the table.