SQL INSERT, UPDATE and DELETE basics

INSERT, UPDATE and DELETE

Reading data is half the job. The other half is changing it. Three commands do that.

INSERT adds a new row. Name the columns, then give the values in the same order:

INSERT INTO inventory (item, qty, price) VALUES ('Glitter pens', 30, 1.5);

You can skip the id here: in SQLite an INTEGER PRIMARY KEY column fills itself in with the next number.

UPDATE changes rows that already exist. SET says what to change, WHERE says which rows:

UPDATE inventory SET price = 0.75 WHERE item = 'Eraser';

DELETE removes rows:

DELETE FROM inventory WHERE qty = 0;

Now the most famous mistake in all of SQL. Forget the WHERE:

UPDATE inventory SET price = 0.75;

That is a perfectly valid command. The database happily sets every price in the shop to 75p, says "done", and does not ask if you are sure. DELETE FROM inventory; without a WHERE empties the whole table. Real companies have lost real data this way, and somewhere right now a developer is staring at a screen in silence.

Habits that save you:

Write the WHERE first, as a SELECT, and check it returns exactly the rows you mean.
Then turn that SELECT into the UPDATE or DELETE, keeping the same WHERE.
Run a SELECT afterwards to see the result.

In the exercises here, finish with SELECT * FROM inventory; so the checker can see your changed table. The sample database resets every time you run, so go ahead and try the disaster version once. It is oddly educational.

Tables in this lesson

inventory 7 rows

id	item	qty	price
1	Pencil	120	0.25
2	Eraser	45	0.5
3	Ruler	0	1.0
4	Highlighter	60	1.25
5	Scientific calculator	4	12.0
6	Gel pen	0	1.75
7	Sticky notes	80	2.0

Show the SQL that built them

CREATE TABLE inventory (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER, price REAL);
INSERT INTO inventory VALUES
 (1, 'Pencil', 120, 0.25),
 (2, 'Eraser', 45, 0.5),
 (3, 'Ruler', 0, 1.0),
 (4, 'Highlighter', 60, 1.25),
 (5, 'Scientific calculator', 4, 12.0),
 (6, 'Gel pen', 0, 1.75),
 (7, 'Sticky notes', 80, 2.0);

Try it yourself

Edit it. Break it. Run it again.

UPDATE inventory SET price = 0.75 WHERE item = 'Eraser';
INSERT INTO inventory (item, qty, price) VALUES ('Glitter pens', 30, 1.5);
SELECT * FROM inventory;

Your turn

Type it yourself. That is the whole trick.

1Restock

A delivery arrived: set the qty of the Ruler to 50. Do not change any other row. Then show the whole table with SELECT * FROM inventory;.

UPDATE inventory SET qty = 50;
SELECT * FROM inventory;

2Clear the empty shelves

Delete every item whose qty is 0, then show the whole table.

SELECT * FROM inventory;

3New stock

Add a new item: Protractor, quantity 25, price 0.8. Then show the whole table.

SELECT * FROM inventory;