Learn/SQL/7. Changing data
Lesson 7 of 8
Sign in

SQL, lesson 7

Changing data

Until now you only read data. Three statements change it: INSERT adds rows, UPDATE changes values in existing rows, and DELETE removes rows. They print nothing themselves, so to see the effect you run a SELECT afterwards.

INSERT: add rows

Name the table, list the columns you are filling, then give the values in the same order:

Listing the columns is optional, but it makes the statement safe if the table later gains a column, and any column you leave out gets its default value (or NULL). When a column is declared INTEGER PRIMARY KEY, SQLite fills it with the next free number if you leave it out.

UPDATE: change values

UPDATE sets new values on every row that matches the condition. The new value can be computed from the old one:

2 exercises to try what you just read.

Exercise 1

Restock the shelf

Press Ctrl Enter or Check to test your code.

Exercise 2

Spring cleaning

Press Ctrl Enter or Check to test your code.

WHERE

All right-hand sides are worked out from the row as it was before the update, so the order of assignments in SET does not change the result.

DELETE: remove rows

The WHERE clause is your safety catch

Both UPDATE and DELETE without a WHERE apply to every row. DELETE FROM pantry; empties the whole table, and there is no undo button. A good habit: write the WHERE first as a SELECT, check that it returns exactly the rows you mean, then turn it into an UPDATE or DELETE.

Text values in conditions are compared exactly, so WHERE item = 'rice' does not match 'Rice'.

Your exercises

In this lesson the harness runs a SELECT after your statements and prints the whole table, so the expected output shows what the table must look like when you are done. Your own code should not print anything.

Further reading

  • SQLite: INSERT
  • SQLite: UPDATE
  • SQLite: DELETE

The table stock has the columns item and qty:

A delivery has arrived. Write two statements:

  1. Add 3 to the qty of Bread.
  2. Insert a new row for Butter with a qty of 4.

After your code, the harness runs SELECT item, qty FROM stock ORDER BY item;. Expected output:

The table tasks has the columns id, title and done (1 means finished, 0 means not):

Write two statements:

  1. Delete every task that is done.
  2. Mark task 4 as done.

After your code, the harness runs SELECT id, title, done FROM tasks ORDER BY id;. Expected output:

INSERT INTO pantry (item, qty) VALUES ('Rice', 3);
INSERT INTO pantry (item, qty) VALUES ('Oats', 1), ('Pasta', 4);  -- several rows at once
UPDATE pantry SET qty = qty + 2 WHERE item = 'Rice';
UPDATE pantry SET qty = 0, note = 'used up' WHERE item = 'Oats';  -- two columns at once
DELETE FROM pantry WHERE qty = 0;
SELECT * FROM pantry WHERE qty = 0;   -- check first
DELETE FROM pantry WHERE qty = 0;     -- then act
item     | qty
Milk     | 2
Eggs     | 12
Bread    | 0
Bread|3
Butter|4
Eggs|12
Milk|2
id | title            | done
1  | Wash the car     | 1
2  | Fix the fence    | 0
3  | Sort the garage  | 1
4  | Paint the shed   | 0
5  | Clean windows    | 0
2|Fix the fence|0
4|Paint the shed|1
5|Clean windows|0