Updating rows
Databases & SQL Lesson 15 1:53 English narration · English + 中文 subtitles burned in
Chapters
Transcript
UPDATE changes values in rows that already exist.
UPDATE 修改已经存在的行里的值。
SET says which column gets which new value, and WHERE says which rows it applies to.
SET 说明哪一列换成什么新值，而 WHERE 说明它作用在哪些行上。
Here the WHERE picks the pad, so the price changes in just that one row.
这里 WHERE 挑中了那个便签本，所以价格只在那一行里变了。
The other two are untouched, and so is every other column of the pad.
另外两行没被碰过，便签本自己的其他列也没被碰过。
One statement can change several columns.
一条语句可以修改好几列。
There is still only one SET, with the columns separated by commas, and each item is column equals new value.
SET 仍然只有一个，各列之间用逗号分开， 而每一项都是"列 = 新值"。
The WHERE is written once and one WHERE covers them all — so every column named changes in exactly the rows it picked.
WHERE 只写一次，一个 WHERE 管住它们全部—— 所以点到名的每一列，都只在它挑中的那些行里发生变化。
Now watch what happens without it.
现在看看没有它会怎样。
Same statement, WHERE removed.
同一条语句，WHERE 去掉。
All three prices become nought point nine five — the pen, the pad and the glue, at once.
三个价格全都变成了 0.95——钢笔、便签本、胶水，一次全变。
There is no warning at all: the database did exactly what you told it, the statement succeeded, and the old prices are gone.
完全没有任何警告： 数据库做的正是你叫它做的事，语句成功了，而旧的价格没了。
That is why nearly every UPDATE you write should have a WHERE.
这就是为什么你写的几乎每一条 UPDATE 都应该带一个 WHERE。
Here is the habit that prevents it, and professionals use it too.
这是能防住它的那个习惯，专业人士也这么做。
Before an UPDATE, run the same WHERE as a SELECT and look at what comes back.
在 UPDATE 之前，把同样的 WHERE 当作一条 SELECT 跑一遍，看看回来的是什么。
If the SELECT returns exactly those rows you meant, put the same WHERE on the UPDATE and run it.
如果 SELECT 返回的正是你要的那些行， 就把同一个 WHERE 放到 UPDATE 上，然后运行。
If it returns more, you have just saved yourself.
如果它返回得更多，那你刚刚救了自己一命。
Four things to take with you.
带走四点。
One: UPDATE changes values in rows that already exist.
第一：UPDATE 修改已经存在的行里的值。
Two: SET lists the columns, separated by commas.
第二：SET 列出各列，用逗号分开。
Three: an UPDATE with no WHERE changes EVERY row.
第三：不带 WHERE 的 UPDATE 会改动每一行。
Four: run the WHERE as a SELECT first.
第四：先把 WHERE 当成 SELECT 跑一遍。
Now run the tasks below.
现在去做下面的题。