Postgresql 中文操作指南

6.2. Updating Data #

对已经位于数据库中的数据的修改称为更新。可以更新单个行、表中的所有行或所有行的子集。每列都可以单独更新；其他列不受影响。

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:

回想一下 Chapter 5 ，SQL 通常不会为行提供唯一标识符。因此，并不总是可以直接指定要更新的行。相反，你需要指定行必须满足哪些条件才能更新。只有当你在表中有主键（无论你是否声明了主键）时，你才可以可靠地选择与主键匹配的条件来寻址各个行。图形数据库访问工具依赖这一事实，以便你可以逐个更新行。

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 的产品，使其价格变为 10：

For example, this command updates all products that have a price of 5 to have a price of 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.

我们详细查看一下该命令。首先是关键词 UPDATE，后跟表名。与往常一样，表名可以是模式限定的，否则会在路径中查找它。接下来是关键词 SET，后跟列名、一个等号和新的列值。新的列值可以是任何标量表达式，而不仅仅是一个常量。例如，如果您想将所有产品的价格上调 10%，可以使用：

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 products SET price = price * 1.10;

正如你所看到的，新值的表达式可以引用行中现有的值。我们还省略了 WHERE 子句。如果省略它，则表示将更新表中的所有行。如果存在它，则只更新与 WHERE 条件匹配的行。请注意，SET 子句中的等号是赋值，而 WHERE 子句中的等号是比较，但这不会产生任何歧义。当然，WHERE 子句不必是相等性测试。还可以使用许多其他运算符（请参阅 Chapter 9 ）。但表达式需要计算为布尔结果。

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.

您可以在 UPDATE 命令中列出 SET 子句中一个以上的赋值，从而更新多个列。例如：

You can update more than one column in an UPDATE command by listing more than one assignment in the SET clause. For example:

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;