>_ IzzyLab

Hands-on task

This task is step 7 of the course “Your first database”

UPDATE: a new price for the kettle

easy~5 min

The kettle has gone up in price. Change the price of the Kettle product in the goods table (database /root/shop.db) to 27.

The prices and other data of the other products must stay as they were.

What you'll practise:
  • sqlite3
  • update
  • where

Topic breakdown

UPDATE changes values in rows that are already in the table:

UPDATE books SET year = 1937 WHERE title = 'The Hobbit';

SET says which column to change and to what, and WHERE — in which rows. The condition in WHERE is the same as in SELECT.

The main rule: UPDATE without WHERE changes every row of the table, and an ordinary command can't undo it. So first you run a SELECT with the same WHERE, look at what matches the condition, and only then write the UPDATE. On a production database people play it safe in two more ways: they make a backup (for SQLite that's just a copy of the database file) and run changes inside a transaction — after BEGIN; the changes can be undone with ROLLBACK; or made permanent with COMMIT;. The same goes for DELETE: DELETE FROM books without a condition deletes everything.

What's checked

  1. The kettle has the new price
  2. The other products haven't changed

What it looks like