Fulfledge

Insert, update, delete

INPUT · Slides

Changing a value

01 / 05

You only want to fix the value

There are fewer of something, or it has moved. Deleting the row and putting it back each time would be a bit much.

Changing the values of a row that is already there is UPDATE. You aim at just the column you want to fix.

02 / 05

The shape of UPDATE

It comes apart into three.

  • UPDATE tools … which table
  • SET stock = 5 … which column to what value
  • WHERE id = 2 … which rows

SET means to set — that is, to put a new value in.

UPDATE tools SET stock = 5  WHERE id = 2;SELECT * FROM tools ORDER BY id;

Result

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

03 / 05

Several columns at once

What follows SET can be separated with commas. One statement fixes as many columns as you like.

And the right-hand side takes more than plain values.

  • SET memo = NULL … put it back to empty
  • SET stock = stock + 5 … work it out from the value it holds now
UPDATE tools  SET place = 'store', stock = 10  WHERE id = 1;SELECT * FROM tools ORDER BY id;

Result

1 | skipping rope | store | the cord is worn | 10
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

04 / 05

Forget the WHERE and everything changes

This is the most important thing in the chapter. Without a WHERE, every row in the table gets the same value.

There is no error. Every row quietly changes, which is what makes it hard to notice.

UPDATE tools SET place = 'store';SELECT * FROM tools ORDER BY id;

Result

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

05 / 05

Check your aim with a SELECT first

The safe way is settled. SELECT with the same WHERE, look at what it catches, and then change it.

If two rows came back, two rows will change. If it caught more than you meant, fix the condition. Let us write some.

SELECT id, name, place FROM tools  WHERE place = 'locker';

Result

3 | race numbers | locker
6 | whistle | locker