Changing Data: INSERT, UPDATE, DELETE

Introduction

Until now you only read data. Apps also need to add, change and remove it. These statements change the database, so they deserve care. In this course every Run starts from a fresh copy of the shop database, so you can try them safely.

Making a table

CREATE TABLE defines a new table: its name, its columns, and the type of each.

Common types are INTEGER (whole numbers), REAL (decimals), TEXT (words) and NUMERIC. PRIMARY KEY marks the column that identifies each row, and NOT NULL means a value is required. The next course explains keys in depth.

The table starts empty, so the SELECT has no rows to show.

INSERT: add rows

List the column names, then the values in the same order. We left out id, so the database filled it in with 1, 2 and so on. You can insert several rows in one statement:

UPDATE: change rows

UPDATE changes existing rows. SET says what to change, and WHERE says which rows.

You can calculate from the current value. This raises every Stationery price by 10 percent, rounded to 2 decimal places:

DELETE: remove rows

The most important rule

Always write a WHERE clause for UPDATE and DELETE. Without it, they apply to every row. Run this and see what happens to the prices:

In a real database there is no undo button. A good habit is to write the SELECT first, with the same WHERE, check that it returns the rows you expect, and only then change it into an UPDATE or DELETE.

Removing a table

DROP TABLE deletes a table and everything in it. Use it with great care.

The last line asks SQLite's built-in catalogue which tables exist. temp_notes is gone.

Try it yourself

Insert a new product called Sticky Notes in the Stationery category with price 2.25 and stock 300. Then update its stock to 250, and finally select all Stationery products to check.

Summary

• CREATE TABLE defines columns and types. DROP TABLE removes a table.
• INSERT INTO ... VALUES adds rows, matching values to the listed columns.
• UPDATE ... SET ... WHERE changes rows, and DELETE FROM ... WHERE removes them.
• Without WHERE, UPDATE and DELETE hit every row. Check with a SELECT first.