Fulfledge

Insert, update, delete

INPUT · Slides

Adding a row

01 / 05

Reading is not enough

So far you have practised reading data with SELECT. But an app also adds new rows, fixes values and gets rid of rows it no longer wants.

This chapter teaches the three statements that change data. You will use a tools table of the kit in a school sports store.

  • id … the number
  • name … what the thing is
  • place … where it is kept
  • memo … anything noticed (sometimes empty)
  • stock … how many there are
SELECT * FROM tools ORDER BY id;

Result

1 | skipping rope | shelf | the cord is worn | 12
2 | ball | basket |  | 8
3 | race numbers | locker |  | 20
4 | timer | shelf | change the battery | 3
5 | marker cone | store |  | 15
6 | whistle | locker | used often | 4

02 / 05

Adding a row with INSERT INTO

The statement that adds a row is INSERT INTO. You write two sets of brackets.

  • the first … which columns you are filling
  • the second … the values

The rule is that what goes inside the VALUES brackets is in the same order as the columns listed above it.

INSERT INTO tools  (id, name, place, memo, stock)  VALUES  (7, 'mat', 'store', NULL, 2);

03 / 05

Three ways to write a value

Values are written differently depending on what they are.

  • text … in single quotes, as in 'mat'
  • numbers … written plainly, as in 2
  • left empty … written as NULL (no quotes)

A column left out of the brackets also gets NULL. Leave memo out and it stays empty.

04 / 05

Add it, then check with SELECT

INSERT returns no table. Run it and the screen stays quiet.

So write a SELECT after it and see with your own eyes whether it went in the way you meant. Separate them with ; and they run in order, top to bottom.

The questions in this chapter come with the checking SELECT already in place. Leave it there and add your writing statement above it.

INSERT INTO tools  (id, name, place, memo, stock)  VALUES  (7, 'mat', 'store', NULL, 2);SELECT * FROM tools ORDER BY id;

Result

1 | skipping rope | shelf | the cord is worn | 12
2 | ball | basket |  | 8
3 | race numbers | locker |  | 20
4 | timer | shelf | change the battery | 3
5 | marker cone | store |  | 15
6 | whistle | locker | used often | 4
7 | mat | store |  | 2

05 / 05

You can add as many rows as you like

Separate what follows VALUES with commas and one INSERT adds as many rows as you want.

Writing two INSERT statements gives the same result. Pick whichever reads better. Let us write some.

INSERT INTO tools  (id, name, place, memo, stock)  VALUES  (7, 'baton', 'basket', NULL, 24),  (8, 'tape measure', 'shelf', NULL, 1);SELECT * FROM tools ORDER BY id;

Result

1 | skipping rope | shelf | the cord is worn | 12
2 | ball | basket |  | 8
3 | race numbers | locker |  | 20
4 | timer | shelf | change the battery | 3
5 | marker cone | store |  | 15
6 | whistle | locker | used often | 4
7 | baton | basket |  | 24
8 | tape measure | shelf |  | 1