Fulfledge

Insert, update, delete

INPUT · Slides

Deleting a row

01 / 05

When the whole row has to go

A piece of kit that broke and was thrown out has no business staying in the record.

Deleting the whole row is DELETE. Unlike UPDATE, which fixes a column's value, it is the row entire that goes, so there is no SET.

02 / 05

The shape of DELETE FROM

The shape is DELETE FROM table WHERE condition;. Conditions are written exactly as they are in a SELECT.

There is no choosing columns here, so no column names go behind DELETE. That is what it looks like next to a SELECT.

DELETE FROM tools WHERE id = 4;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
5 | marker cone | store |  | 15
6 | whistle | locker | used often | 4

03 / 05

Everything matching goes together

If two rows meet the WHERE, both go. There is no guarantee that only one row goes.

Keep the difference in mind between a condition that settles on one row, like id, and one that catches many, like place.

DELETE FROM tools  WHERE place = 'locker';SELECT * FROM tools ORDER BY id;

Result

1 | skipping rope | shelf | the cord is worn | 12
2 | ball | basket |  | 8
4 | timer | shelf | change the battery | 3
5 | marker cone | store |  | 15

04 / 05

Without a WHERE it all goes

A DELETE with no WHERE empties the table. Run it and this is what happens.

The table itself stays, but with no rows in it the checking SELECT returns nothing.

There is no need to be frightened. You only need to know how to check first.

DELETE FROM tools;SELECT * FROM tools ORDER BY id;

Result

(nothing comes back)

05 / 05

Count before you delete

The safe way is to SELECT with the same condition first and look at what it catches. If you only want the number, COUNT(*) is quicker.

Delete knowing it is two rows and nothing more will go than you expected. Let us write some.

SELECT COUNT(*) FROM tools  WHERE stock < 5;

Result

2