{"Entry":{"collection":"sql","key":"update","name":"UPDATE","aliases":["update"],"metadata":{"aliases":["update"],"changed_in":["7.0","7.1","7.4","8.2","8.3","8.4","9.0","9.1","9.2","9.5","10","12","18"],"changes":[{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["UPDATE table SET col = expression [, ...]"],"removed":["UPDATE table SET column = expression [, ...]"]},"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-update.htm","to_file":"sql-update.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["UPDATE [ ONLY ] table SET col = expression [, ...]"],"removed":[]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","outputs","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]"],"removed":["UPDATE [ ONLY ] table SET col = expression [, ...]"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["description","parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","outputs","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["UPDATE [ ONLY ] table [ [ AS ] alias ]","SET { column = { expression | DEFAULT } |","( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]","[ RETURNING * | output_expression [ AS output_name ] [, ...] ]"],"removed":[]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]","[ WHERE condition | WHERE CURRENT OF cursor_name ]"],"removed":[]},"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]"],"removed":[]},"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["[ FROM from_list ]"],"removed":["[ FROM fromlist ]"]},"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH [ RECURSIVE ] with_query [, ...] ]"],"removed":[]},"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","outputs"],"removed":[]},"status":"changed","synopsis":{"added":["UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]","SET { column_name = { expression | DEFAULT } |","( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]"],"removed":["UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]","SET { column = { expression | DEFAULT } |","( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]"]},"to":"9.2"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |","( column_name [, ...] ) = ( sub-SELECT )","[ FROM from_item [, ...] ]"],"removed":["[ FROM from_list ]"]},"to":"9.5"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |"],"removed":[]},"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]"],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["examples"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]"],"removed":[]},"to":"18"}],"content_hash":"79bb393a4e2938fa64e3569e1b8976a9003eaf731a3e2da31586bdd066f3c0b0","editorial":{},"first_version":"6.4","group":"query","imported_at":"2026-09-27T17:57:27.1815+08:00","last_version":"20","name":"UPDATE","object":"","position":11007,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"update rows of a table","purpose_zh":"","related":[],"slug":"update","source_rev":"b7bd9cda","synopsis":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nUPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]\nSET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ FROM from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]","verb":"UPDATE"}},"Definition":{"Collection":"sql","Key":"update","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"update","SourceRevision":"b7bd9cda","Facts":{"anchor":"SQL-UPDATE","file":"sql-update.html","lang":"en","name":"UPDATE","purpose":"update rows of a table","purpose_zh":"","related":[],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause; columns not explicitly modified retain their previous values.\u003c/p\u003e\u003cp\u003eThere are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e clause. Which technique is more appropriate depends on the specific circumstances.\u003c/p\u003e\u003cp\u003eThe optional \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause causes \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e, can be computed. By default, the new (post-update) values of the table's columns are used, but it is also possible to request the old (pre-update) values. The syntax of the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list is identical to that of the output list of \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eYou must have the \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e privilege on the table, or at least on the column(s) that are listed to be updated. You must also have the \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on any column whose values are read in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpressions\u003c/code\u003e\u003c/em\u003e or \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause allows you to specify one or more subqueries that can be referenced by name in the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e query. See \u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH Queries (Common Table Expressions)\"\u003eSection 7.8\u003c/a\u003e and \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the table to update. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is specified before the table name, matching rows are updated in the named table only. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is not specified, matching rows are also updated in any tables inheriting from the named table. Optionally, \u003ccode class=\"literal\"\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that descendant tables are included.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given \u003ccode class=\"literal\"\u003eUPDATE foo AS f\u003c/code\u003e, the remainder of the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e statement must refer to this table as \u003ccode class=\"literal\"\u003ef\u003c/code\u003e not \u003ccode class=\"literal\"\u003efoo\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a column in the table named by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, \u003ccode class=\"literal\"\u003eUPDATE table_name SET table_name.col = 1\u003c/code\u003e is invalid.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to assign to the column. The expression can use the old values of this and other columns in the table.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSet the column to its default value (which will be NULL if no specific default expression has been assigned to it). An identity column will be set to a new value generated by the associated sequence. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to old values of the current row of the table being updated.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA table expression allowing columns from other tables to appear in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition and update expressions. This uses the same syntax as the \u003ca href=\"/docs/18/sql-select.html#SQL-FROM\" title=\"FROM Clause\"\u003e\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e\u003c/a\u003e clause of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement; for example, an alias for the table name can be specified. Do not repeat the target table as a \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e unless you intend a self-join (in which case it must appear with an alias in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression that returns a value of type \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e. Only rows for which this expression returns \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e will be updated.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the cursor to use in a \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e condition. The row to be updated is the one most recently fetched from this cursor. The cursor must be a non-grouping query on the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e's target table. Note that \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e cannot be specified together with a Boolean condition. See \u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\u003c/span\u003e\u003c/a\u003e for more information about using cursors with \u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional substitute name for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e rows in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list.\u003c/p\u003e\u003cp\u003eBy default, old values from the target table can be returned by writing \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e, and new values can be returned by writing \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to be computed and returned by the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e command after each row is updated. The expression can use any column names of the table named by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e or table(s) listed in \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e. Write \u003ccode class=\"literal\"\u003e*\u003c/code\u003e to return all columns.\u003c/p\u003e\u003cp\u003eA column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e may be qualified using \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, or the corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, to cause old or new values to be returned. An unqualified column name, or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e, or a column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e qualified using the target table name or alias will return new values.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA name to use for a returned column.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eOn successful completion, an \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e command returns a command tag of the form\u003c/p\u003e\u003cpre class=\"screen\"\u003eUPDATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is the number of rows updated, including matched rows whose values did not change. Note that the number may be less than the number of rows that matched the \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e when updates were suppressed by a \u003ccode class=\"literal\"\u003eBEFORE UPDATE\u003c/code\u003e trigger. If \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is 0, no rows were updated by the query (this is not considered an error).\u003c/p\u003e\u003cp\u003eIf the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e command contains a \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause, the result will be similar to that of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement containing the columns and values defined in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list, computed over the row(s) updated by the command.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003eWhen a \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e clause is present, what essentially happens is that the target table is joined to the tables mentioned in the \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e list, and each output row of the join represents an update operation for the target table. When using \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.\u003c/p\u003e\u003cp\u003eBecause of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.\u003c/p\u003e\u003cp\u003eIn the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition. In that case, if there is some other partition in the partition tree for which this row satisfies its partition constraint, then the row is moved to that partition. If there is no such partition, an error will occur. Behind the scenes, the row movement is actually a \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e and \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e operation.\u003c/p\u003e\u003cp\u003eThere is a possibility that a concurrent \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e or \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e on the row being moved will get a serialization failure error. Suppose session 1 is performing an \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e on a partition key, and meanwhile a concurrent session 2 for which this row is visible performs an \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e or \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e operation on this row. In such case, session 2's \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e or \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e will detect the row movement and raise a serialization failure error (which always returns with an SQLSTATE code '40001'). Applications may wish to retry the transaction if this occurs. In the usual case where the table is not partitioned, or where there is no row movement, session 2 would have identified the newly updated row and carried out the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e/\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e on this new row version.\u003c/p\u003e\u003cp\u003eNote that while rows can be moved from local partitions to a foreign-table partition (provided the foreign data wrapper supports tuple routing), they cannot be moved from a foreign-table partition to another partition.\u003c/p\u003e\u003cp\u003eAn attempt of moving a row from one partition to another will fail if a foreign key is found to directly reference an ancestor of the source partition that is not the same as the ancestor that's mentioned in the \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e query.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eChange the word \u003ccode class=\"literal\"\u003eDrama\u003c/code\u003e to \u003ccode class=\"literal\"\u003eDramatic\u003c/code\u003e in the column \u003ccode class=\"structfield\"\u003ekind\u003c/code\u003e of the table \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';\n\u003c/pre\u003e\u003cp\u003eAdjust temperature entries and reset precipitation to its default value in one row of the table \u003ccode class=\"structname\"\u003eweather\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT\n  WHERE city = 'San Francisco' AND date = '2003-07-03';\n\u003c/pre\u003e\u003cp\u003ePerform the same operation and return the updated entries, and the old precipitation value:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT\n  WHERE city = 'San Francisco' AND date = '2003-07-03'\n  RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;\n\u003c/pre\u003e\u003cp\u003eUse the alternative column-list syntax to do the same update:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)\n  WHERE city = 'San Francisco' AND date = '2003-07-03';\n\u003c/pre\u003e\u003cp\u003eIncrement the sales count of the salesperson who manages the account for Acme Corporation, using the \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e clause syntax:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE employees SET sales_count = sales_count + 1 FROM accounts\n  WHERE accounts.name = 'Acme Corporation'\n  AND employees.id = accounts.sales_person;\n\u003c/pre\u003e\u003cp\u003ePerform the same operation, using a sub-select in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE employees SET sales_count = sales_count + 1 WHERE id =\n  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');\n\u003c/pre\u003e\u003cp\u003eUpdate contact names in an accounts table to match the currently assigned salespeople:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE accounts SET (contact_first_name, contact_last_name) =\n    (SELECT first_name, last_name FROM employees\n     WHERE employees.id = accounts.sales_person);\n\u003c/pre\u003e\u003cp\u003eA similar result could be accomplished with a join:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE accounts SET contact_first_name = first_name,\n                    contact_last_name = last_name\n  FROM employees WHERE employees.id = accounts.sales_person;\n\u003c/pre\u003e\u003cp\u003eHowever, the second query may give unexpected results if \u003ccode class=\"structname\"\u003eemployees\u003c/code\u003e.\u003ccode class=\"structfield\"\u003eid\u003c/code\u003e is not a unique key, whereas the first query is guaranteed to raise an error if there are multiple \u003ccode class=\"structfield\"\u003eid\u003c/code\u003e matches. Also, if there is no match for a particular \u003ccode class=\"structname\"\u003eaccounts\u003c/code\u003e.\u003ccode class=\"structfield\"\u003esales_person\u003c/code\u003e entry, the first query will set the corresponding name fields to NULL, whereas the second query will not update that row at all.\u003c/p\u003e\u003cp\u003eUpdate statistics in a summary table to match the current data:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =\n    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d\n     WHERE d.group_id = s.group_id);\n\u003c/pre\u003e\u003cp\u003eAttempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. To do this without failing the entire transaction, use savepoints:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN;\n-- other operations\nSAVEPOINT sp1;\nINSERT INTO wines VALUES('Chateau Lafite 2003', '24');\n-- Assume the above fails because of a unique key violation,\n-- so now we issue these commands:\nROLLBACK TO sp1;\nUPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';\n-- continue with other operations, and eventually\nCOMMIT;\n\u003c/pre\u003e\u003cp\u003eChange the \u003ccode class=\"structfield\"\u003ekind\u003c/code\u003e column of the table \u003ccode class=\"structname\"\u003efilms\u003c/code\u003e in the row on which the cursor \u003ccode class=\"literal\"\u003ec_films\u003c/code\u003e is currently positioned:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;\n\u003c/pre\u003e\u003cp\u003eUpdates affecting many rows can have negative effects on system performance, such as table bloat, increased replica lag, and increased lock contention. In such situations it can make sense to perform the operation in smaller batches, possibly with a \u003ccode class=\"command\"\u003eVACUUM\u003c/code\u003e operation on the table between batches. While there is no \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e clause for \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, it is possible to get a similar effect through the use of a \u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH Queries (Common Table Expressions)\"\u003eCommon Table Expression\u003c/a\u003e and a self-join. With the standard \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e table access method, a self-join on the system column \u003ca href=\"/docs/18/ddl-system-columns.html#DDL-SYSTEM-COLUMNS-CTID\"\u003ectid\u003c/a\u003e is very efficient:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eWITH exceeded_max_retries AS (\n  SELECT w.ctid FROM work_item AS w\n    WHERE w.status = 'active' AND w.num_retries \u0026gt; 10\n    ORDER BY w.retry_timestamp\n    FOR UPDATE\n    LIMIT 5000\n)\nUPDATE work_item SET status = 'failed'\n  FROM exceeded_max_retries AS emr\n  WHERE work_item.ctid = emr.ctid;\n\u003c/pre\u003e\u003cp\u003eThis command will need to be repeated until no rows remain to be updated. (This use of \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e is only safe because the query is repeatedly run, avoiding the problem of changed \u003ccode class=\"structfield\"\u003ectid\u003c/code\u003es.) Use of an \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clause allows the command to prioritize which rows will be updated; it can also prevent deadlock with other update operations if they use the same ordering. If lock contention is a concern, then \u003ccode class=\"literal\"\u003eSKIP LOCKED\u003c/code\u003e can be added to the \u003cacronym\u003eCTE\u003c/acronym\u003e to prevent multiple commands from updating the same row. However, then a final \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e without \u003ccode class=\"literal\"\u003eSKIP LOCKED\u003c/code\u003e or \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e will be needed to ensure that no matching rows were overlooked.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThis command conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard, except that the \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e and \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clauses are \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extensions, as is the ability to use \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e with \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eSome other database systems offer a \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e option in which the target table is supposed to be listed again within \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e. That is not how \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e interprets \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e. Be careful when porting applications that use this extension.\u003c/p\u003e\u003cp\u003eAccording to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e only allows the source value to be a \u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" title=\"4.2.13. Row Constructors\"\u003erow constructor\u003c/a\u003e or a sub-\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e. An individual column's updated value can be specified as \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e in the row-constructor case, but not inside a sub-\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e.\u003c/p\u003e","key":"compatibility","title":"Compatibility"}],"sections_same_as":"","slug":"18","synopsis_html":"[ WITH [ RECURSIVE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e [, ...] ]\nUPDATE [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e ]\n    SET { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e = { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } |\n          ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = [ ROW ] ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } [, ...] ) |\n          ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e )\n        } [, ...]\n    [ FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e [, ...] ]\n    [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e | WHERE CURRENT OF \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e ]\n    [ RETURNING [ WITH ( { OLD | NEW } AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n                { * | \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e ] } [, ...] ]","synopsis_text":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nUPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]\nSET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ FROM from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"update","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"UPDATE","Summary":"更新表中的行","BodyHTML":"\u003cpre\u003e[ WITH [ RECURSIVE ] with_query [, ...] ]\nUPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]\nSET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ FROM from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eUPDATE\u003c/code\u003e会更改所有满足条件的行中指定列的值。只需在\u003ccode\u003eSET\u003c/code\u003e子句中提及需要修改的列；未被显式修改的列会保留其原有值。\u003c/p\u003e\u003cp\u003e有两种方法可以利用数据库中其他表所包含的信息来修改一个表：使用子选择，或者在\u003ccode\u003eFROM\u003c/code\u003e子句中指定附加表。哪种技术更合适取决于具体情况。\u003c/p\u003e\u003cp\u003e可选的\u003ccode\u003eRETURNING\u003c/code\u003e子句使\u003ccode\u003eUPDATE\u003c/code\u003e 基于每个实际更新的行计算并返回一个或多个值。可以计算任何使用该表列和/或 \u003ccode\u003eFROM\u003c/code\u003e中提到的其他表列的表达式。默认使用该表列的新值（更新后值），但也可以请求旧值（更新前值）。\u003ccode\u003eRETURNING\u003c/code\u003e列表的语法与\u003ccode\u003eSELECT\u003c/code\u003e 的输出列表相同。\u003c/p\u003e\u003cp\u003e必须拥有该表上的\u003ccode\u003eUPDATE\u003c/code\u003e权限，或者至少拥有待更新列的\u003ccode\u003eUPDATE\u003c/code\u003e权限。对于其值会在 \u003cem\u003e\u003ccode\u003eexpressions\u003c/code\u003e\u003c/em\u003e或者 \u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e中读取的任何列，还必须拥有\u003ccode\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eWITH\u003c/code\u003e子句允许你指定一个或多个子查询，这些子查询可在\u003ccode\u003eUPDATE\u003c/code\u003e查询中按名称引用。详见\u003ca href=\"/docs/18/queries-with.html\" rel=\"nofollow\"\u003e第 7.8 节\u003c/a\u003e和\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要更新的表名（可以是模式限定的）。如果在表名前指定了 \u003ccode\u003eONLY\u003c/code\u003e，只会更新所提及表中的匹配行。如果未指定 \u003ccode\u003eONLY\u003c/code\u003e，还会更新继承自该表的任何表中的匹配行。可选地，可以在表名后指定\u003ccode\u003e*\u003c/code\u003e，以显式指示包含后代表。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e目标表的替代名称。提供别名时，它会完全隐藏该表的实际名称。例如，给定\u003ccode\u003eUPDATE foo AS f\u003c/code\u003e，\u003ccode\u003eUPDATE\u003c/code\u003e语句的其余部分必须将该表称为 \u003ccode\u003ef\u003c/code\u003e，而不是\u003ccode\u003efoo\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e由\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e命名的表中的列名。如有需要，列名可以使用子字段名或数组下标进行限定。指定目标列时不要包含表名 — 例如，\u003ccode\u003eUPDATE table_name SET table_name.col = 1\u003c/code\u003e是无效的。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要赋给该列的表达式。该表达式可以使用该表中这一列和其他列的旧值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e把该列设置为其默认值（如果没有为它指定特定的默认表达式，则该值为 NULL）。标识列将被设置为由关联序列生成的新值。对于生成列，允许指定这一项，但这仅仅指定了根据其生成表达式计算该列的常规行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个\u003ccode\u003eSELECT\u003c/code\u003e子查询，其输出列数必须与它前面圆括号中列列表的列数相同。执行时，该子查询返回的行数不得超过一行。如果返回一行，则其列值会赋给目标列；如果不返回任何行，则把 NULL 值赋给目标列。该子查询可以引用正在更新的表的当前行的旧值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个表表达式，允许其他表的列出现在\u003ccode\u003eWHERE\u003c/code\u003e条件和更新表达式中。这里使用的语法与\u003ccode\u003eSELECT\u003c/code\u003e语句的\u003ca href=\"/docs/18/sql-select.html#SQL-FROM\" title=\"FROM 子句\" rel=\"nofollow\"\u003e\u003ccode\u003eFROM\u003c/code\u003e\u003c/a\u003e子句相同；例如，可以为表名指定别名。除非你打算进行自连接（这种情况下它必须在\u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e中带有别名出现），否则不要把目标表重复写成\u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个返回\u003ccode\u003eboolean\u003c/code\u003e类型值的表达式。只有使这个表达式返回\u003ccode\u003etrue\u003c/code\u003e的行才会被更新。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要在\u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e条件中使用的游标名。要被更新的行是最近一次从该游标中取出的那一行。该游标必须是针对\u003ccode\u003eUPDATE\u003c/code\u003e目标表的非分组查询。注意，\u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e不能与布尔条件同时指定。有关将游标用于\u003ccode\u003eWHERE CURRENT OF\u003c/code\u003e的更多信息，请见\u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\" rel=\"nofollow\"\u003e\u003cspan\u003eDECLARE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在\u003ccode\u003eRETURNING\u003c/code\u003e列表中为\u003ccode\u003eOLD\u003c/code\u003e或 \u003ccode\u003eNEW\u003c/code\u003e行指定的可选替代名称。\u003c/p\u003e\u003cp\u003e默认情况下，可通过 \u003ccode\u003eOLD.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode\u003eOLD.*\u003c/code\u003e返回目标表旧值，通过 \u003ccode\u003eNEW.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode\u003eNEW.*\u003c/code\u003e返回新值。提供别名后，这些名称会被隐藏，必须使用别名来引用旧行或新行。例如 \u003ccode\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每一行被更新后，由\u003ccode\u003eUPDATE\u003c/code\u003e命令计算并返回的表达式。该表达式可以使用由\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 命名的表或\u003ccode\u003eFROM\u003c/code\u003e中列出的表中的任何列名。写成 \u003ccode\u003e*\u003c/code\u003e可返回所有列。\u003c/p\u003e\u003cp\u003e列名或\u003ccode\u003e*\u003c/code\u003e可以用\u003ccode\u003eOLD\u003c/code\u003e或 \u003ccode\u003eNEW\u003c/code\u003e进行限定，也可以用 \u003ccode\u003eOLD\u003c/code\u003e或\u003ccode\u003eNEW\u003c/code\u003e对应的 \u003cem\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e进行限定，以返回旧值或新值。未限定的列名或\u003ccode\u003e*\u003c/code\u003e，以及使用目标表名或别名限定的列名或\u003ccode\u003e*\u003c/code\u003e，都会返回新值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于返回列的名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e输出\u003c/h2\u003e\u003cp\u003e在成功完成时，一个\u003ccode\u003eUPDATE\u003c/code\u003e命令会返回以下形式的命令标签：\u003c/p\u003e\u003cpre\u003eUPDATE \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是被更新的行数，包括值没有改变的匹配行。注意，当更新被\u003ccode\u003eBEFORE UPDATE\u003c/code\u003e 触发器抑制时，这个数量可能小于匹配 \u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e的行数。如果 \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e为 0，则该查询没有更新任何行（这不被视为错误）。\u003c/p\u003e\u003cp\u003e如果\u003ccode\u003eUPDATE\u003c/code\u003e命令包含\u003ccode\u003eRETURNING\u003c/code\u003e 子句，其结果将类似于一个\u003ccode\u003eSELECT\u003c/code\u003e语句，其中包含 \u003ccode\u003eRETURNING\u003c/code\u003e列表中定义的列和值，并在该命令更新的行上进行计算。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e当存在\u003ccode\u003eFROM\u003c/code\u003e子句时，本质上会将目标表与 \u003cem\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e列表中提到的表连接起来，而连接的每一条输出行都代表对目标表的一次更新操作。使用 \u003ccode\u003eFROM\u003c/code\u003e时，应确保对每一个要修改的目标行，连接至多生成一条输出行。换言之，一条目标行不应与其他表中的多于一行成功连接。如果发生这种情况，则只会使用其中某一条连接行来更新目标行，但具体使用哪一条并不容易预测。\u003c/p\u003e\u003cp\u003e因为存在这种不确定性，所以仅在子选择中引用其他表会更安全，尽管这种写法通常比使用连接更难阅读、也更慢。\u003c/p\u003e\u003cp\u003e对于分区表，更新一行可能会导致它不再满足其所在分区的分区约束。在这种情况下，如果在分区树中存在另一个分区，且该行满足它的分区约束，则该行会被移动到那个分区。如果不存在这样的分区，则会报错。在幕后，行移动实际上是一次\u003ccode\u003eDELETE\u003c/code\u003e和一次 \u003ccode\u003eINSERT\u003c/code\u003e操作。\u003c/p\u003e\u003cp\u003e在被移动的行上并发执行\u003ccode\u003eUPDATE\u003c/code\u003e或 \u003ccode\u003eDELETE\u003c/code\u003e时，有可能收到串行化失败错误。假设会话 1 正在对某个分区键执行\u003ccode\u003eUPDATE\u003c/code\u003e，与此同时，一个能够看到该行的并发会话 2 对该行执行\u003ccode\u003eUPDATE\u003c/code\u003e或\u003ccode\u003eDELETE\u003c/code\u003e操作。在这种情况下，会话 2 的\u003ccode\u003eUPDATE\u003c/code\u003e或 \u003ccode\u003eDELETE\u003c/code\u003e将检测到行移动，并引发串行化失败错误（其 SQLSTATE 代码始终为\u0026#39;40001\u0026#39;）。如果发生这种情况，应用程序可能需要重试事务。在表未分区或没有发生行移动的通常情况下，会话 2 会识别出新更新的那一行，并在这个新行版本上执行 \u003ccode\u003eUPDATE\u003c/code\u003e/\u003ccode\u003eDELETE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e请注意，虽然行可以从本地分区移动到外部表分区（前提是外部数据包装器支持元组路由），但不能从外部表分区移动到另一个分区。\u003c/p\u003e\u003cp\u003e如果发现某个外键直接引用了源分区的某个祖先，并且这个祖先与 \u003ccode\u003eUPDATE\u003c/code\u003e查询中提到的祖先不是同一个，那么尝试把一行从一个分区移动到另一个分区将会失败。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e把表\u003ccode\u003efilms\u003c/code\u003e的\u003ccode\u003ekind\u003c/code\u003e 列中的单词\u003ccode\u003eDrama\u003c/code\u003e改为\u003ccode\u003eDramatic\u003c/code\u003e：\u003c/p\u003e\u003cpre\u003eUPDATE films SET kind = \u0026#39;Dramatic\u0026#39; WHERE kind = \u0026#39;Drama\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e在表\u003ccode\u003eweather\u003c/code\u003e的一行中调整温度项，并将降水量重置为其默认值：\u003c/p\u003e\u003cpre\u003eUPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT\n  WHERE city = \u0026#39;San Francisco\u0026#39; AND date = \u0026#39;2003-07-03\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e执行同样的操作，并返回更新后的各项以及旧的降水值：\u003c/p\u003e\u003cpre\u003eUPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT\n  WHERE city = \u0026#39;San Francisco\u0026#39; AND date = \u0026#39;2003-07-03\u0026#39;\n  RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;\n\u003c/pre\u003e\u003cp\u003e使用另一种列列表语法完成同样的更新：\u003c/p\u003e\u003cpre\u003eUPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)\n  WHERE city = \u0026#39;San Francisco\u0026#39; AND date = \u0026#39;2003-07-03\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e使用\u003ccode\u003eFROM\u003c/code\u003e子句语法，将负责 Acme Corporation 账户的销售人员的销量计数加一：\u003c/p\u003e\u003cpre\u003eUPDATE employees SET sales_count = sales_count + 1 FROM accounts\n  WHERE accounts.name = \u0026#39;Acme Corporation\u0026#39;\n  AND employees.id = accounts.sales_person;\n\u003c/pre\u003e\u003cp\u003e在\u003ccode\u003eWHERE\u003c/code\u003e子句中使用子选择，执行同样的操作：\u003c/p\u003e\u003cpre\u003eUPDATE employees SET sales_count = sales_count + 1 WHERE id =\n  (SELECT sales_person FROM accounts WHERE name = \u0026#39;Acme Corporation\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e更新\u003ccode\u003eaccounts\u003c/code\u003e表中的联系人姓名，使其与当前分配的销售人员保持一致：\u003c/p\u003e\u003cpre\u003eUPDATE accounts SET (contact_first_name, contact_last_name) =\n    (SELECT first_name, last_name FROM employees\n     WHERE employees.id = accounts.sales_person);\n\u003c/pre\u003e\u003cp\u003e使用连接也可以得到类似结果：\u003c/p\u003e\u003cpre\u003eUPDATE accounts SET contact_first_name = first_name,\n                    contact_last_name = last_name\n  FROM employees WHERE employees.id = accounts.sales_person;\n\u003c/pre\u003e\u003cp\u003e但是，如果\u003ccode\u003eemployees\u003c/code\u003e.\u003ccode\u003eid\u003c/code\u003e不是唯一键，第二个查询可能会给出意外结果；而如果存在多个 \u003ccode\u003eid\u003c/code\u003e匹配，第一个查询则保证会报错。此外，如果某个特定的\u003ccode\u003eaccounts\u003c/code\u003e.\u003ccode\u003esales_person\u003c/code\u003e 条目没有匹配项，第一个查询会把相应的姓名字段设为\u003ccode\u003eNULL\u003c/code\u003e，而第二个查询则根本不会更新该行。\u003c/p\u003e\u003cp\u003e更新汇总表中的统计数据以匹配当前数据：\u003c/p\u003e\u003cpre\u003eUPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =\n    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d\n     WHERE d.group_id = s.group_id);\n\u003c/pre\u003e\u003cp\u003e尝试插入一个新库存项及其库存量。如果该项已存在，则改为更新现有项的库存量。要在不致使整个事务失败的情况下做到这一点，请使用保存点：\u003c/p\u003e\u003cpre\u003eBEGIN;\n-- 其他操作\nSAVEPOINT sp1;\nINSERT INTO wines VALUES(\u0026#39;Chateau Lafite 2003\u0026#39;, \u0026#39;24\u0026#39;);\n-- 假设上面的语句因唯一键冲突而失败，\n-- 那么现在发出这些命令：\nROLLBACK TO sp1;\nUPDATE wines SET stock = stock + 24 WHERE winename = \u0026#39;Chateau Lafite 2003\u0026#39;;\n-- 继续执行其他操作，最后\nCOMMIT;\n\u003c/pre\u003e\u003cp\u003e更改表\u003ccode\u003efilms\u003c/code\u003e中游标\u003ccode\u003ec_films\u003c/code\u003e 当前定位那一行的\u003ccode\u003ekind\u003c/code\u003e列：\u003c/p\u003e\u003cpre\u003eUPDATE films SET kind = \u0026#39;Dramatic\u0026#39; WHERE CURRENT OF c_films;\n\u003c/pre\u003e\u003cp\u003e影响很多行的更新可能对系统性能产生负面影响，例如表膨胀、复制延迟增加以及锁争用加剧。在这类场景中，将操作分成较小批次来执行可能更有意义，并且可以在各批次之间对该表执行\u003ccode\u003eVACUUM\u003c/code\u003e。虽然\u003ccode\u003eUPDATE\u003c/code\u003e没有\u003ccode\u003eLIMIT\u003c/code\u003e子句，但可以通过\u003ca href=\"/docs/18/queries-with.html\" rel=\"nofollow\"\u003e公共表表达式\u003c/a\u003e和自连接获得类似效果。对于标准的\u003cspan\u003ePostgreSQL\u003c/span\u003e 表访问方法，基于系统列\u003ca href=\"/docs/18/ddl-system-columns.html#DDL-SYSTEM-COLUMNS-CTID\" rel=\"nofollow\"\u003ectid\u003c/a\u003e 的自连接非常高效：\u003c/p\u003e\u003cpre\u003eWITH exceeded_max_retries AS (\n  SELECT w.ctid FROM work_item AS w\n    WHERE w.status = \u0026#39;active\u0026#39; AND w.num_retries \u0026gt; 10\n    ORDER BY w.retry_timestamp\n    FOR UPDATE\n    LIMIT 5000\n)\nUPDATE work_item SET status = \u0026#39;failed\u0026#39;\n  FROM exceeded_max_retries AS emr\n  WHERE work_item.ctid = emr.ctid;\n\u003c/pre\u003e\u003cp\u003e该命令需要重复执行，直到没有剩余行需要更新。（这里使用\u003ccode\u003ectid\u003c/code\u003e之所以安全，仅仅是因为查询会重复运行，从而避免了\u003ccode\u003ectid\u003c/code\u003e发生变化带来的问题。）使用\u003ccode\u003eORDER BY\u003c/code\u003e子句可以让命令确定优先更新哪些行；如果其他更新操作也使用相同的顺序，它还可以防止死锁。如果担心锁争用，可以在\u003cacronym\u003eCTE\u003c/acronym\u003e中加入\u003ccode\u003eSKIP LOCKED\u003c/code\u003e，以防多个命令更新同一行。不过，这样仍需要在最后执行一次不带 \u003ccode\u003eSKIP LOCKED\u003c/code\u003e或\u003ccode\u003eLIMIT\u003c/code\u003e的 \u003ccode\u003eUPDATE\u003c/code\u003e，以确保没有遗漏任何匹配行。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e这个命令符合\u003cacronym\u003eSQL\u003c/acronym\u003e标准，不过 \u003ccode\u003eFROM\u003c/code\u003e和\u003ccode\u003eRETURNING\u003c/code\u003e子句是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展，把\u003ccode\u003eWITH\u003c/code\u003e 与\u003ccode\u003eUPDATE\u003c/code\u003e一起使用的能力也是扩展。\u003c/p\u003e\u003cp\u003e某些其他数据库系统提供一种\u003ccode\u003eFROM\u003c/code\u003e选项，要求在 \u003ccode\u003eFROM\u003c/code\u003e中再次列出目标表。\u003cspan\u003ePostgreSQL\u003c/span\u003e 并不是这样解释\u003ccode\u003eFROM\u003c/code\u003e的。在移植使用这种扩展的应用时要小心。\u003c/p\u003e\u003cp\u003e根据标准，目标列名的一个圆括号子列表的源值可以是任何能够产生正确列数的行值表达式。\u003cspan\u003ePostgreSQL\u003c/span\u003e只允许该源值是一个\u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" rel=\"nofollow\"\u003e行构造器\u003c/a\u003e或子\u003ccode\u003eSELECT\u003c/code\u003e。在行构造器的情况下，单个列的更新值可以指定为\u003ccode\u003eDEFAULT\u003c/code\u003e，但在子\u003ccode\u003eSELECT\u003c/code\u003e 中则不能这样做。\u003c/p\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"437e05bffbd7060b3e5f57ee112c7e643b57acabd4fafe8a979252d183baca16","Payload":{"purpose_zh":"更新表中的行","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e会更改所有满足条件的行中指定列的值。只需在\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e子句中提及需要修改的列；未被显式修改的列会保留其原有值。\u003c/p\u003e\u003cp\u003e有两种方法可以利用数据库中其他表所包含的信息来修改一个表：使用子选择，或者在\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e子句中指定附加表。哪种技术更合适取决于具体情况。\u003c/p\u003e\u003cp\u003e可选的\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句使\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e 基于每个实际更新的行计算并返回一个或多个值。可以计算任何使用该表列和/或 \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e中提到的其他表列的表达式。默认使用该表列的新值（更新后值），但也可以请求旧值（更新前值）。\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e列表的语法与\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e 的输出列表相同。\u003c/p\u003e\u003cp\u003e必须拥有该表上的\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e权限，或者至少拥有待更新列的\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e权限。对于其值会在 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpressions\u003c/code\u003e\u003c/em\u003e或者 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e中读取的任何列，还必须拥有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e子句允许你指定一个或多个子查询，这些子查询可在\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e查询中按名称引用。详见\u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH查询（公共表表达式）\"\u003e第 7.8 节\u003c/a\u003e和\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要更新的表名（可以是模式限定的）。如果在表名前指定了 \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，只会更新所提及表中的匹配行。如果未指定 \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，还会更新继承自该表的任何表中的匹配行。可选地，可以在表名后指定\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以显式指示包含后代表。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e目标表的替代名称。提供别名时，它会完全隐藏该表的实际名称。例如，给定\u003ccode class=\"literal\"\u003eUPDATE foo AS f\u003c/code\u003e，\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e语句的其余部分必须将该表称为 \u003ccode class=\"literal\"\u003ef\u003c/code\u003e，而不是\u003ccode class=\"literal\"\u003efoo\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e由\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e命名的表中的列名。如有需要，列名可以使用子字段名或数组下标进行限定。指定目标列时不要包含表名 — 例如，\u003ccode class=\"literal\"\u003eUPDATE table_name SET table_name.col = 1\u003c/code\u003e是无效的。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要赋给该列的表达式。该表达式可以使用该表中这一列和其他列的旧值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e把该列设置为其默认值（如果没有为它指定特定的默认表达式，则该值为 NULL）。标识列将被设置为由关联序列生成的新值。对于生成列，允许指定这一项，但这仅仅指定了根据其生成表达式计算该列的常规行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e子查询，其输出列数必须与它前面圆括号中列列表的列数相同。执行时，该子查询返回的行数不得超过一行。如果返回一行，则其列值会赋给目标列；如果不返回任何行，则把 NULL 值赋给目标列。该子查询可以引用正在更新的表的当前行的旧值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个表表达式，允许其他表的列出现在\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件和更新表达式中。这里使用的语法与\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句的\u003ca href=\"/docs/18/sql-select.html#SQL-FROM\" title=\"FROM 子句\"\u003e\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e\u003c/a\u003e子句相同；例如，可以为表名指定别名。除非你打算进行自连接（这种情况下它必须在\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e中带有别名出现），否则不要把目标表重复写成\u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个返回\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e类型值的表达式。只有使这个表达式返回\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e的行才会被更新。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要在\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e条件中使用的游标名。要被更新的行是最近一次从该游标中取出的那一行。该游标必须是针对\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e目标表的非分组查询。注意，\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e不能与布尔条件同时指定。有关将游标用于\u003ccode class=\"literal\"\u003eWHERE CURRENT OF\u003c/code\u003e的更多信息，请见\u003ca href=\"/docs/18/sql-declare.html\" title=\"DECLARE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDECLARE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e列表中为\u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e行指定的可选替代名称。\u003c/p\u003e\u003cp\u003e默认情况下，可通过 \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e返回目标表旧值，通过 \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e返回新值。提供别名后，这些名称会被隐藏，必须使用别名来引用旧行或新行。例如 \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每一行被更新后，由\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e命令计算并返回的表达式。该表达式可以使用由\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 命名的表或\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e中列出的表中的任何列名。写成 \u003ccode class=\"literal\"\u003e*\u003c/code\u003e可返回所有列。\u003c/p\u003e\u003cp\u003e列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e可以用\u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e进行限定，也可以用 \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e或\u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e对应的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e进行限定，以返回旧值或新值。未限定的列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以及使用目标表名或别名限定的列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，都会返回新值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于返回列的名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e在成功完成时，一个\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e命令会返回以下形式的命令标签：\u003c/p\u003e\u003cpre class=\"screen\"\u003eUPDATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是被更新的行数，包括值没有改变的匹配行。注意，当更新被\u003ccode class=\"literal\"\u003eBEFORE UPDATE\u003c/code\u003e 触发器抑制时，这个数量可能小于匹配 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e的行数。如果 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e为 0，则该查询没有更新任何行（这不被视为错误）。\u003c/p\u003e\u003cp\u003e如果\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e命令包含\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 子句，其结果将类似于一个\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句，其中包含 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e列表中定义的列和值，并在该命令更新的行上进行计算。\u003c/p\u003e","key":"outputs","title":"输出"},{"html":"\u003cp\u003e当存在\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e子句时，本质上会将目标表与 \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e列表中提到的表连接起来，而连接的每一条输出行都代表对目标表的一次更新操作。使用 \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e时，应确保对每一个要修改的目标行，连接至多生成一条输出行。换言之，一条目标行不应与其他表中的多于一行成功连接。如果发生这种情况，则只会使用其中某一条连接行来更新目标行，但具体使用哪一条并不容易预测。\u003c/p\u003e\u003cp\u003e因为存在这种不确定性，所以仅在子选择中引用其他表会更安全，尽管这种写法通常比使用连接更难阅读、也更慢。\u003c/p\u003e\u003cp\u003e对于分区表，更新一行可能会导致它不再满足其所在分区的分区约束。在这种情况下，如果在分区树中存在另一个分区，且该行满足它的分区约束，则该行会被移动到那个分区。如果不存在这样的分区，则会报错。在幕后，行移动实际上是一次\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e和一次 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e操作。\u003c/p\u003e\u003cp\u003e在被移动的行上并发执行\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e时，有可能收到串行化失败错误。假设会话 1 正在对某个分区键执行\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e，与此同时，一个能够看到该行的并发会话 2 对该行执行\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e操作。在这种情况下，会话 2 的\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e将检测到行移动，并引发串行化失败错误（其 SQLSTATE 代码始终为'40001'）。如果发生这种情况，应用程序可能需要重试事务。在表未分区或没有发生行移动的通常情况下，会话 2 会识别出新更新的那一行，并在这个新行版本上执行 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e/\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e请注意，虽然行可以从本地分区移动到外部表分区（前提是外部数据包装器支持元组路由），但不能从外部表分区移动到另一个分区。\u003c/p\u003e\u003cp\u003e如果发现某个外键直接引用了源分区的某个祖先，并且这个祖先与 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e查询中提到的祖先不是同一个，那么尝试把一行从一个分区移动到另一个分区将会失败。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e把表\u003ccode class=\"structname\"\u003efilms\u003c/code\u003e的\u003ccode class=\"structfield\"\u003ekind\u003c/code\u003e 列中的单词\u003ccode class=\"literal\"\u003eDrama\u003c/code\u003e改为\u003ccode class=\"literal\"\u003eDramatic\u003c/code\u003e：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';\n\u003c/pre\u003e\u003cp\u003e在表\u003ccode class=\"structname\"\u003eweather\u003c/code\u003e的一行中调整温度项，并将降水量重置为其默认值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT\n  WHERE city = 'San Francisco' AND date = '2003-07-03';\n\u003c/pre\u003e\u003cp\u003e执行同样的操作，并返回更新后的各项以及旧的降水值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT\n  WHERE city = 'San Francisco' AND date = '2003-07-03'\n  RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;\n\u003c/pre\u003e\u003cp\u003e使用另一种列列表语法完成同样的更新：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)\n  WHERE city = 'San Francisco' AND date = '2003-07-03';\n\u003c/pre\u003e\u003cp\u003e使用\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e子句语法，将负责 Acme Corporation 账户的销售人员的销量计数加一：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE employees SET sales_count = sales_count + 1 FROM accounts\n  WHERE accounts.name = 'Acme Corporation'\n  AND employees.id = accounts.sales_person;\n\u003c/pre\u003e\u003cp\u003e在\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句中使用子选择，执行同样的操作：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE employees SET sales_count = sales_count + 1 WHERE id =\n  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');\n\u003c/pre\u003e\u003cp\u003e更新\u003ccode class=\"structname\"\u003eaccounts\u003c/code\u003e表中的联系人姓名，使其与当前分配的销售人员保持一致：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE accounts SET (contact_first_name, contact_last_name) =\n    (SELECT first_name, last_name FROM employees\n     WHERE employees.id = accounts.sales_person);\n\u003c/pre\u003e\u003cp\u003e使用连接也可以得到类似结果：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE accounts SET contact_first_name = first_name,\n                    contact_last_name = last_name\n  FROM employees WHERE employees.id = accounts.sales_person;\n\u003c/pre\u003e\u003cp\u003e但是，如果\u003ccode class=\"structname\"\u003eemployees\u003c/code\u003e.\u003ccode class=\"structfield\"\u003eid\u003c/code\u003e不是唯一键，第二个查询可能会给出意外结果；而如果存在多个 \u003ccode class=\"structfield\"\u003eid\u003c/code\u003e匹配，第一个查询则保证会报错。此外，如果某个特定的\u003ccode class=\"structname\"\u003eaccounts\u003c/code\u003e.\u003ccode class=\"structfield\"\u003esales_person\u003c/code\u003e 条目没有匹配项，第一个查询会把相应的姓名字段设为\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e，而第二个查询则根本不会更新该行。\u003c/p\u003e\u003cp\u003e更新汇总表中的统计数据以匹配当前数据：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =\n    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d\n     WHERE d.group_id = s.group_id);\n\u003c/pre\u003e\u003cp\u003e尝试插入一个新库存项及其库存量。如果该项已存在，则改为更新现有项的库存量。要在不致使整个事务失败的情况下做到这一点，请使用保存点：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN;\n-- 其他操作\nSAVEPOINT sp1;\nINSERT INTO wines VALUES('Chateau Lafite 2003', '24');\n-- 假设上面的语句因唯一键冲突而失败，\n-- 那么现在发出这些命令：\nROLLBACK TO sp1;\nUPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';\n-- 继续执行其他操作，最后\nCOMMIT;\n\u003c/pre\u003e\u003cp\u003e更改表\u003ccode class=\"structname\"\u003efilms\u003c/code\u003e中游标\u003ccode class=\"literal\"\u003ec_films\u003c/code\u003e 当前定位那一行的\u003ccode class=\"structfield\"\u003ekind\u003c/code\u003e列：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eUPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;\n\u003c/pre\u003e\u003cp\u003e影响很多行的更新可能对系统性能产生负面影响，例如表膨胀、复制延迟增加以及锁争用加剧。在这类场景中，将操作分成较小批次来执行可能更有意义，并且可以在各批次之间对该表执行\u003ccode class=\"command\"\u003eVACUUM\u003c/code\u003e。虽然\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e没有\u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e子句，但可以通过\u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH查询（公共表表达式）\"\u003e公共表表达式\u003c/a\u003e和自连接获得类似效果。对于标准的\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 表访问方法，基于系统列\u003ca href=\"/docs/18/ddl-system-columns.html#DDL-SYSTEM-COLUMNS-CTID\"\u003ectid\u003c/a\u003e 的自连接非常高效：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eWITH exceeded_max_retries AS (\n  SELECT w.ctid FROM work_item AS w\n    WHERE w.status = 'active' AND w.num_retries \u0026gt; 10\n    ORDER BY w.retry_timestamp\n    FOR UPDATE\n    LIMIT 5000\n)\nUPDATE work_item SET status = 'failed'\n  FROM exceeded_max_retries AS emr\n  WHERE work_item.ctid = emr.ctid;\n\u003c/pre\u003e\u003cp\u003e该命令需要重复执行，直到没有剩余行需要更新。（这里使用\u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e之所以安全，仅仅是因为查询会重复运行，从而避免了\u003ccode class=\"structfield\"\u003ectid\u003c/code\u003e发生变化带来的问题。）使用\u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e子句可以让命令确定优先更新哪些行；如果其他更新操作也使用相同的顺序，它还可以防止死锁。如果担心锁争用，可以在\u003cacronym\u003eCTE\u003c/acronym\u003e中加入\u003ccode class=\"literal\"\u003eSKIP LOCKED\u003c/code\u003e，以防多个命令更新同一行。不过，这样仍需要在最后执行一次不带 \u003ccode class=\"literal\"\u003eSKIP LOCKED\u003c/code\u003e或\u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e的 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e，以确保没有遗漏任何匹配行。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e这个命令符合\u003cacronym\u003eSQL\u003c/acronym\u003e标准，不过 \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e和\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展，把\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e 与\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e一起使用的能力也是扩展。\u003c/p\u003e\u003cp\u003e某些其他数据库系统提供一种\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e选项，要求在 \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e中再次列出目标表。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 并不是这样解释\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e的。在移植使用这种扩展的应用时要小心。\u003c/p\u003e\u003cp\u003e根据标准，目标列名的一个圆括号子列表的源值可以是任何能够产生正确列数的行值表达式。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e只允许该源值是一个\u003ca href=\"/docs/18/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS\" title=\"4.2.13. 行构造器\"\u003e行构造器\u003c/a\u003e或子\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e。在行构造器的情况下，单个列的更新值可以指定为\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e，但在子\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e 中则不能这样做。\u003c/p\u003e","key":"compatibility","title":"兼容性"}],"sections_same_as":"","synopsis_html":"[ WITH [ RECURSIVE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e [, ...] ]\nUPDATE [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e ]\n    SET { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e = { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } |\n          ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = [ ROW ] ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } [, ...] ) |\n          ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e )\n        } [, ...]\n    [ FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003efrom_item\u003c/code\u003e\u003c/em\u003e [, ...] ]\n    [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e | WHERE CURRENT OF \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecursor_name\u003c/code\u003e\u003c/em\u003e ]\n    [ RETURNING [ WITH ( { OLD | NEW } AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n                { * | \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e ] } [, ...] ]","synopsis_text":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nUPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]\nSET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ FROM from_item [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursor_name ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}