NAME
UPDATE - 更新一个表中的行
SYNOPSIS
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...] [ FROM fromlist ] [ WHERE condition ]
DESCRIPTION 描述
UPDATE 改变满足条件的所有行的声明了的列/字段的值。 只有要更改的列/字段需要在语句中出现,没有明确的 SET 的字段保持它们原来的数值。
缺省时,UPDATE 将更新所声明的表和所有子表的记录。 如果你希望只更新所声明的表,你应该使用 ONLY 子句。
要更改表,你必须对它有UPDATE 权限, 同样对 expression 或者 condition 条件里提到的任何表也要有SELECT权限。
PARAMETERS 参数
- table
现存表的名称(可以有模式修饰)。- column
表 table 中列/字段的名。- expression
赋予列/字段的一个有效的值或表达式。表达式可以使用表中这个或其它字段的旧数值。- DEFAULT
把字段设置为它的缺省值(如果没有缺省表达式赋予它,那么就是 NULL)。- fromlist
一个表达式的列表,允许来自其它表中的列/字段出现在 WHERE 条件里。- condition
一个表达式,返回 boolean 类型。只有这个表达式返回 true 的行被更新。
OUTPUTS 输出
成功完成后,UPDATE 命令返回形如
UPDATE count
的命令标签。count 是更新的行数。 如果 count 是 0, 那么没有符合 condition 的行(这个不认为是错误)。
EXAMPLES 例子
把表 films 里的字段 kind 里的词 Drama 用Dramatic 代替:
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
调整表 weather 中的一行的温度记录并且把降水设置为缺省值:
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03';
COMPATIBILITY 兼容性
这条命令遵循 SQL 标准。FROM 子句是 PostgreSQL 扩展。
#p#
NAME
UPDATE - update rows of a table
SYNOPSIS
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...] [ FROM fromlist ] [ WHERE condition ]
DESCRIPTION
UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the statement; columns not explicitly SET retain their previous values.
By default, UPDATE will update rows in the specified table and all its subtables. If you wish to only update the specific table mentioned, you must use the ONLY clause.
You must have the UPDATE privilege on the table to update it, as well as the SELECT privilege to any table whose values are read in the expressions or condition.
PARAMETERS
- table
- The name (optionally schema-qualified) of the table to update.
- column
- The name of a column in table.
- expression
- An expression to assign to the column. The expression may use the old values of this and other columns in the table.
- DEFAULT
- Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).
- fromlist
- A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions.
- condition
- An expression that returns a value of type boolean. Only rows for which this expression returns true will be updated.
OUTPUTS
On successful completion, an UPDATE command returns a command tag of the form
UPDATE count
The count is the number of rows updated. If count is 0, no rows matched the condition (this is not considered an error).
EXAMPLES
Change the word Drama to Dramatic in the column kind of the table films:
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
Adjust temperature entries and reset precipitation to its default value in one row of the table weather:
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03';
COMPATIBILITY
This command conforms to the SQL standard. The FROM clause is a PostgreSQL extension.