Garden› Spring› BETAfeedback
field guide❋ Page 1 of 13 palette size
SQL · Garden
in which the gardener gets her hands dirty
❋
— Chapter —
II

Tending the Bed

in which the gardener picks up trowel, shears, and gloves
and changes the bed itself — planting, pruning, pulling
❋
Spring · Tending the Bed

Page 2 · Welcome

Until now, you've only looked

today you start to change what's there

Until now you only looked. In Chapter I you walked the bed and read the flowers with SELECT. You asked the bed questions. The bed answered. Nothing in the bed was moved.

Today is different. The bed will change. You will plant a rose where there was none. You will prune one shorter. You will paint another a new color. You will pull a dying one out of the soil.

There are three verbs for this work. INSERT puts a flower in. UPDATE changes one that is there. DELETE takes one away. You will meet each. By the end of the day the bed will be different, and you will be the one who made it different.

Trowel, shears, gloves — and a bed of five roses waiting.

Page 3 · Three new verbs

Three tools, three verbs

INSERT plants, UPDATE prunes, DELETE pulls

Each verb has its own shape — its own syntax — but they all share one thing: they change the bed instead of just looking at it. After running one, the rose_bed table will be different. The roses you can see, count, and walk past will be different.

INSERT INTO
plant a new flower in the bed — adds one or more rows
UPDATE … SET
change a flower already there — modifies existing rows
DELETE FROM
pull a flower out — removes rows

All three accept the familiar WHERE from Chapter I. With INSERT, WHERE isn't used (you're planting something new). With UPDATE and DELETE, WHERE decides which flowers the change applies to. Forget the WHERE and you'll change every flower in the bed — sometimes that's what you want, but more often it's a mistake the bed can't undo.

Page 4 · INSERT

Plant a flower

INSERT INTO rose_bed VALUES (...)

To plant a new rose, name the table you're planting into, list the columns you're filling, and list the values for each. The shape of an INSERT looks like a small recipe:

INSERT INTO rose_bed (id, species, color, height) VALUES (6, 'Pink Lady', 'pink', 17);

Press Run query. A new rose appears in the bed.

tending.sql
the rose bed
INSERT
Run the query to plant the new rose.
Field guide: the column list before VALUES matches the value list inside the parentheses, in order. Text values go in single quotes; numbers don't. The query returns "1 row inserted" — your status message confirms what happened.

Shortcut: you can drop the column list entirely — INSERT INTO rose_bed VALUES (7, 'Mary Rose', 'pink', 19); — but then you must supply every column in the table, in the order it was defined. Try the no column list chip to see it. Most experienced gardeners stick with the explicit column list — it's longer, but it survives the bed gaining a new column without breaking.

Page 5 · INSERT multiple rows

Plant several at once

multiple value rows, comma-separated

You can plant more than one rose in a single statement. After VALUES, list one set of values per new flower, each set in its own parentheses, separated by commas. Two new roses join the bed — including Sun Flare, a yellow rose. The color column is just text; the bed will accept any colour name you write. (In Chapter III you'll see how to add a rule that restricts it to a chosen list.)

tending.sql
the rose bed
INSERT (2 rows)
Two roses, planted in one statement.
Field guide: bulk inserts are faster than running many single-row inserts. The status message tells you how many rows were planted in total.

Page 6 · UPDATE

Prune a rose taller

UPDATE rose_bed SET height = ... WHERE ...

Iceberg (id 4) is only 16 cm — the shortest rose in the bed. Suppose she's had a strong season — measure her again at 24 cm and write the new height into her tag. The verb is UPDATE, followed by which column to change, set to which value, and which rose to apply it to:

tending.sql
the rose bed
UPDATE
Iceberg grows from 16 cm to 24 cm — a strong season.
Field guide: UPDATE changes existing rows. SET col = value names the column and its new value. WHERE is the filter that decides which rows the change applies to.

Page 7 · UPDATE multiple columns

Repot and repaint

several SET clauses, comma-separated

You can change more than one column at once. Carefree Beauty (id 5) has been pruned taller and her petals have faded — let's record both at once, her new color and her new height:

tending.sql
the rose bed
UPDATE (2 cols)
Carefree Beauty changes color and height in one statement.
Notice: with one WHERE, you can update many columns at once. Each column gets its own name = value, separated by commas. And the right side of = doesn't have to be a literal — it can be an expression that reads the current row. The grow all pinks +2 chip uses height = height + 2, which means take each rose's current height and add two.

Page 8 · DELETE

Pull a flower

DELETE FROM rose_bed WHERE ...

The third tool is the simplest. DELETE FROM a table, optionally with a WHERE filter, and the matching rows are gone. Let's pull the purple roses — Crimson Glory will be the only casualty in the baseline bed:

tending.sql
the rose bed
DELETE
Crimson Glory is pulled. Four roses remain.
Field guide: DELETE uses the same WHERE as SELECT and UPDATE — the same filter syntax, the same comparison operators. Master WHERE once and you can use it everywhere.

Page 9 · WHERE is everything

WHERE is everything

precision matters most when the bed will actually change

WHERE was first introduced in Chapter I as a filter for SELECT. The same filter shapes UPDATE and DELETE. The only difference: when SELECT misreads a WHERE, you get a wrong answer on screen. When UPDATE or DELETE misreads a WHERE, the bed itself is wrong, and it stays wrong.

Three habits that experienced gardeners share:

  • Read the WHERE out loud before running. "Only roses where color is purple." Say it to yourself. It catches a surprising number of mistakes.
  • SELECT first, mutate second. Run SELECT * FROM rose_bed WHERE color = 'purple' first. Confirm the rows you see are the ones you mean. Then change the SELECT to UPDATE or DELETE.
  • If there's no WHERE, double-check that's intentional. Sometimes it is — you really do want to update or delete everything. But pause and confirm.
-- 1. Look first.
SELECT * FROM rose_bed WHERE color = 'purple';
-- 2. Confirm those are the rows you mean.
-- 3. Then change SELECT * to UPDATE/DELETE.
DELETE FROM rose_bed WHERE color = 'purple';
The two-step gardener: look, then act.

Page 10 · The anatomy of writes

The three shapes

INSERT, UPDATE, DELETE side by side

Each verb has its own template. Memorize the shape; the details follow.

INSERT INTO rose_bed (id, species, color, height)
VALUES (6, 'Pink Lady', 'pink', 17);

UPDATE rose_bed
SET color = 'white', height = 20
WHERE id = 5;

DELETE FROM rose_bed
WHERE color = 'purple';
INSERT INTO
table name, then a list of columns, then VALUES with one set of values per row
UPDATE
table name, then SET column = value (comma-separated), then a WHERE — technically optional, almost always necessary
DELETE FROM
table name, then a WHERE — technically optional, almost always necessary

Drop the WHERE on UPDATE or DELETE and the change applies to every row in the bed. It runs without complaint and cannot be undone — see Page 9.

Page 11 · Tend the bed yourself

Tend the bed yourself

any INSERT, UPDATE, DELETE — or a SELECT to peek

The bed is yours. Try the chips below in order — peek, plant, grow, pull, peek again — to feel a small gardener's workflow. Or write anything you like; the bed keeps each change until you press Reset bed.

scratch.sql
the rose bed
your query
Edit the query, run it, and the bed will reflect your work.
⌘/Ctrl + Enter to run
Result / current bed —
Run a SELECT to see the current bed as a table.

rose_bed — schema

idinteger · primary key
speciestext
colortext
heightinteger · cm
Tip: The chips above load one step at a time. Press Run after each, watch the bed change, then move to the next. Reset clears your work and brings back the original five roses.

Page 12 · End of Chapter II

The bed is yours

what you've learned, and what's next

In the garden of Tending the Bed, the gardener touches the soil itself. Flowers are added where there were none; ones already present are altered; others are pulled from the ground. By the day's end, the bed is no longer the bed of the morning.

INSERT INTO
added new rows to the table
UPDATE … SET
changed existing rows
DELETE FROM
removed rows
WHERE
decided which rows the change applied to — the most important word in the chapter

After this garden lies one more in Spring: it is called the Greenhouse. In it, no flower is yet planted — only a card is drafted, on which the columns of the future bed are named and the rules it will obey are stamped. By such cards, every bed in the garden is shaped before its first seed.

Further on, in Winter, you'll learn how to wrap risky changes so they can be undone if you change your mind. Until then, the Reset bed button is your stand-in. For today, the trowel and shears are cleaned, and the bed is yours.

Check your bed

Three questions to confirm INSERT, UPDATE, and DELETE feel natural. Write a statement and press Check.

1. Plant a new rose: id 6, species 'Sweet Surrender', color 'pink', height 19. Use INSERT.

INSERT INTO rose_bed (id, species, color, height) VALUES (6, 'Sweet Surrender', 'pink', 19);

2. Rename rose id 4 from 'Iceberg' to 'Polar Star', and reset its height to 20. Use UPDATE.

UPDATE rose_bed SET species = 'Polar Star', height = 20 WHERE id = 4;

3. Pull every rose shorter than 18 cm out of the bed. Use DELETE.

DELETE FROM rose_bed WHERE height < 18;

How did this chapter land?

Your verdict
· Sunset ·
❋
end of Chapter
II
the soil is settled, the tools cleaned,
the bed shaped by your own hand
❋
an intermission is recommended · Chapter III awaits