6.2. Updating Data
6.2. Updating Data
6.2.更新数据
The modification of data that is already in the database is referred to as updating. You can update
individual rows, all the rows in a table, or a subset of all rows. Each column can be updated separately;
the other columns are not affected.
使用update对数据库中的数据进行修改。可以更新表中的某行、某几行或所有行。可以分别更新表中的某一列；而其他的列并不会受影响。
To update existing rows, use the UPDATE command. This requires three pieces of information:
使用UPDATE命令更新现有行。需要有以下三方面的信息：
1. The name of the table and column to update
1.表名及需要更新的列
2. The new value of the column
2.列的新值
3. Which row(s) to update
3.更新的行
Recall from Chapter 5 that SQL does not, in general, provide a unique identifier for rows. Therefore it
is not always possible to directly specify which row to update. Instead, you specify which conditions
a row must meet in order to be updated. Only if you have a primary key in the table (independent
of whether you declared it or not) can you reliably address individual rows by choosing a condition
that matches the primary key. Graphical database access tools rely on this fact to allow you to update
rows individually.
回顾一下
第5章
，SQL通常不会为行提供唯一的标识符。因此，并非总是可以直接指定要更新的行。而是指定更新必须满足的条件的行。只有在表中有一个主键（与是否声明它无关），才可以通过选择与主键匹配的条件来可靠地寻址各个行。图形数据库访问工具依靠这一事实来允许您单独更新行。
For example, this command updates all products that have a price of 5 to have a price of 10:
例如，此命令将所有产品中价格为5的更新为10。
UPDATE products SET price = 10 WHERE price = 5;
This might cause zero, one, or many rows to be updated. It is not an error to attempt an update that
does not match any rows.
这可能会更新0行、一行或许多行。尝试更新时未匹配到行，并不是一个错误。
Let's look at that command in detail. First is the key word
UPDATE
followed by the table name. As
usual, the table name can be schema-qualified, otherwise it is looked up in the path. Next is the key
word
SET
followed by the column name, an equal sign, and the new column value. The new column
value can be any scalar expression, not just a constant. For example, if you want to raise the price of
all products by 10% you could use:
让我们来详细分析下此命令。首先，关键字UPDATE后跟着表名。
通常，表名可以以模式限定，否则将在搜索路径中查找表名。接下来是关键字SET，后跟列名称，等号和新列值。新的列值可以是任何标量表达式，而不仅仅是常数。 例如，如果您想将所有产品的价格提高10％，则可以：
UPDATE products SET price = price * 1.10;
As you see, the expression for the new value can refer to the existing value(s) in the row. We also left
out the
WHERE
clause. If it is omitted, it means that all rows in the table are updated. If it is present,
only those rows that match the
WHERE
condition are updated. Note that the equals sign in the
SET
clause is an assignment while the one in the
WHERE
clause is a comparison, but this does not create any
ambiguity. Of course, the
WHERE
condition does not have to be an equality test. Many other operators
are available (see Chapter 9). But the expression needs to evaluate to a Boolean result.
正如所见，新值的表达式可以关联到现有行的值。我们也没有使用where子句。如果省略了where，则意味着表中的所有行都会被更新。如果使用了where，则仅有符合where表达式的行会被更新。请注意，set中的等号是赋值而where中的等号是比较，但这并不会产生什么歧义。当然，where条件不必须是相等比较。也可用其他的操作符（参见
第9章
）。但结果需返回布尔值。
You can update more than one column in an
UPDATE
command by listing more than one assignment
in the
SET
clause. For example:
可以通过在set语句中指定多个赋值以更新多列。例如：
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;