begriffs open source - ai-pg/blob - full-docs/html/sql-update.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>UPDATE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-unlisten.html" title="UNLISTEN" /><link rel="next" href="sql-vacuum.html" title="VACUUM" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">UPDATE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-unlisten.html" title="UNLISTEN">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-vacuum.html" title="VACUUM">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-UPDATE"><div class="titlepage"></div><a id="id-1.9.3.183.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">UPDATE</span></h2><p>UPDATE — update rows of a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   3 [ WITH [ RECURSIVE ] <em class="replaceable"><code>with_query</code></em> [, ...] ]
   4 UPDATE [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ] [ [ AS ] <em class="replaceable"><code>alias</code></em> ]
   5     SET { <em class="replaceable"><code>column_name</code></em> = { <em class="replaceable"><code>expression</code></em> | DEFAULT } |
   6           ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = [ ROW ] ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) |
   7           ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = ( <em class="replaceable"><code>sub-SELECT</code></em> )
   8         } [, ...]
   9     [ FROM <em class="replaceable"><code>from_item</code></em> [, ...] ]
  10     [ WHERE <em class="replaceable"><code>condition</code></em> | WHERE CURRENT OF <em class="replaceable"><code>cursor_name</code></em> ]
  11     [ RETURNING [ WITH ( { OLD | NEW } AS <em class="replaceable"><code>output_alias</code></em> [, ...] ) ]
  12                 { * | <em class="replaceable"><code>output_expression</code></em> [ [ AS ] <em class="replaceable"><code>output_name</code></em> ] } [, ...] ]
  13 </pre></div><div class="refsect1" id="id-1.9.3.183.5"><h2>Description</h2><p>
  14    <code class="command">UPDATE</code> changes the values of the specified
  15    columns in all rows that satisfy the condition. Only the columns to
  16    be modified need be mentioned in the <code class="literal">SET</code> clause;
  17    columns not explicitly modified retain their previous values.
  18   </p><p>
  19    There are two ways to modify a table using information contained in
  20    other tables in the database: using sub-selects, or specifying
  21    additional tables in the <code class="literal">FROM</code> clause. Which
  22    technique is more appropriate depends on the specific
  23    circumstances.
  24   </p><p>
  25    The optional <code class="literal">RETURNING</code> clause causes <code class="command">UPDATE</code>
  26    to compute and return value(s) based on each row actually updated.
  27    Any expression using the table's columns, and/or columns of other
  28    tables mentioned in <code class="literal">FROM</code>, can be computed.
  29    By default, the new (post-update) values of the table's columns are used,
  30    but it is also possible to request the old (pre-update) values.
  31    The syntax of the <code class="literal">RETURNING</code> list is identical to that of the
  32    output list of <code class="command">SELECT</code>.
  33   </p><p>
  34    You must have the <code class="literal">UPDATE</code> privilege on the table,
  35    or at least on the column(s) that are listed to be updated.
  36    You must also have the <code class="literal">SELECT</code>
  37    privilege on any column whose values are read in the
  38    <em class="replaceable"><code>expressions</code></em> or
  39    <em class="replaceable"><code>condition</code></em>.
  40   </p></div><div class="refsect1" id="id-1.9.3.183.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>with_query</code></em></span></dt><dd><p>
  41       The <code class="literal">WITH</code> clause allows you to specify one or more
  42       subqueries that can be referenced by name in the <code class="command">UPDATE</code>
  43       query. See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> and <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>
  44       for details.
  45      </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>
  46       The name (optionally schema-qualified) of the table to update.
  47       If <code class="literal">ONLY</code> is specified before the table name, matching rows
  48       are updated in the named table only.  If <code class="literal">ONLY</code> is not
  49       specified, matching rows are also updated in any tables inheriting from
  50       the named table.  Optionally, <code class="literal">*</code> can be specified after the
  51       table name to explicitly indicate that descendant tables are included.
  52      </p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p>
  53       A substitute name for the target table. When an alias is
  54       provided, it completely hides the actual name of the table.  For
  55       example, given <code class="literal">UPDATE foo AS f</code>, the remainder of the
  56       <code class="command">UPDATE</code> statement must refer to this table as
  57       <code class="literal">f</code> not <code class="literal">foo</code>.
  58      </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
  59       The name of a column in the table named by <em class="replaceable"><code>table_name</code></em>.
  60       The column name can be qualified with a subfield name or array
  61       subscript, if needed.  Do not include the table's name in the
  62       specification of a target column — for example,
  63       <code class="literal">UPDATE table_name SET table_name.col = 1</code> is invalid.
  64      </p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p>
  65       An expression to assign to the column.  The expression can use the
  66       old values of this and other columns in the table.
  67      </p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>
  68       Set the column to its default value (which will be NULL if no specific
  69       default expression has been assigned to it).  An identity column will be
  70       set to a new value generated by the associated sequence.  For a
  71       generated column, specifying this is permitted but merely specifies the
  72       normal behavior of computing the column from its generation expression.
  73      </p></dd><dt><span class="term"><em class="replaceable"><code>sub-SELECT</code></em></span></dt><dd><p>
  74       A <code class="literal">SELECT</code> sub-query that produces as many output columns
  75       as are listed in the parenthesized column list preceding it.  The
  76       sub-query must yield no more than one row when executed.  If it
  77       yields one row, its column values are assigned to the target columns;
  78       if it yields no rows, NULL values are assigned to the target columns.
  79       The sub-query can refer to old values of the current row of the table
  80       being updated.
  81      </p></dd><dt><span class="term"><em class="replaceable"><code>from_item</code></em></span></dt><dd><p>
  82       A table expression allowing columns from other tables to appear in
  83       the <code class="literal">WHERE</code> condition and update expressions. This
  84       uses the same syntax as the <a class="link" href="sql-select.html#SQL-FROM" title="FROM Clause"><code class="literal">FROM</code></a> clause of
  85       a <code class="command">SELECT</code> statement;
  86       for example, an alias for the table name can be specified.  Do not
  87       repeat the target table as a <em class="replaceable"><code>from_item</code></em>
  88       unless you intend a self-join (in which case it must appear with
  89       an alias in the <em class="replaceable"><code>from_item</code></em>).
  90      </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>
  91       An expression that returns a value of type <code class="type">boolean</code>.
  92       Only rows for which this expression returns <code class="literal">true</code>
  93       will be updated.
  94      </p></dd><dt><span class="term"><em class="replaceable"><code>cursor_name</code></em></span></dt><dd><p>
  95       The name of the cursor to use in a <code class="literal">WHERE CURRENT OF</code>
  96       condition.  The row to be updated is the one most recently fetched
  97       from this cursor.  The cursor must be a non-grouping
  98       query on the <code class="command">UPDATE</code>'s target table.
  99       Note that <code class="literal">WHERE CURRENT OF</code> cannot be
 100       specified together with a Boolean condition.  See
 101       <a class="xref" href="sql-declare.html" title="DECLARE"><span class="refentrytitle">DECLARE</span></a>
 102       for more information about using cursors with
 103       <code class="literal">WHERE CURRENT OF</code>.
 104      </p></dd><dt><span class="term"><em class="replaceable"><code>output_alias</code></em></span></dt><dd><p>
 105       An optional substitute name for <code class="literal">OLD</code> or
 106       <code class="literal">NEW</code> rows in the <code class="literal">RETURNING</code> list.
 107      </p><p>
 108       By default, old values from the target table can be returned by writing
 109       <code class="literal">OLD.<em class="replaceable"><code>column_name</code></em></code>
 110       or <code class="literal">OLD.*</code>, and new values can be returned by writing
 111       <code class="literal">NEW.<em class="replaceable"><code>column_name</code></em></code>
 112       or <code class="literal">NEW.*</code>.  When an alias is provided, these names are
 113       hidden and the old or new rows must be referred to using the alias.
 114       For example <code class="literal">RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</code>.
 115      </p></dd><dt><span class="term"><em class="replaceable"><code>output_expression</code></em></span></dt><dd><p>
 116       An expression to be computed and returned by the <code class="command">UPDATE</code>
 117       command after each row is updated.  The expression can use any
 118       column names of the table named by <em class="replaceable"><code>table_name</code></em>
 119       or table(s) listed in <code class="literal">FROM</code>.
 120       Write <code class="literal">*</code> to return all columns.
 121      </p><p>
 122       A column name or <code class="literal">*</code> may be qualified using
 123       <code class="literal">OLD</code> or <code class="literal">NEW</code>, or the corresponding
 124       <em class="replaceable"><code>output_alias</code></em> for
 125       <code class="literal">OLD</code> or <code class="literal">NEW</code>, to cause old or new
 126       values to be returned.  An unqualified column name, or
 127       <code class="literal">*</code>, or a column name or <code class="literal">*</code> qualified
 128       using the target table name or alias will return new values.
 129      </p></dd><dt><span class="term"><em class="replaceable"><code>output_name</code></em></span></dt><dd><p>
 130       A name to use for a returned column.
 131      </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.183.7"><h2>Outputs</h2><p>
 132    On successful completion, an <code class="command">UPDATE</code> command returns a command
 133    tag of the form
 134 </p><pre class="screen">
 135 UPDATE <em class="replaceable"><code>count</code></em>
 136 </pre><p>
 137    The <em class="replaceable"><code>count</code></em> is the number
 138    of rows updated, including matched rows whose values did not change.
 139    Note that the number may be less than the number of rows that matched
 140    the <em class="replaceable"><code>condition</code></em> when
 141    updates were suppressed by a <code class="literal">BEFORE UPDATE</code> trigger.  If
 142    <em class="replaceable"><code>count</code></em> is 0, no rows were
 143    updated by the query (this is not considered an error).
 144   </p><p>
 145    If the <code class="command">UPDATE</code> command contains a <code class="literal">RETURNING</code>
 146    clause, the result will be similar to that of a <code class="command">SELECT</code>
 147    statement containing the columns and values defined in the
 148    <code class="literal">RETURNING</code> list, computed over the row(s) updated by the
 149    command.
 150   </p></div><div class="refsect1" id="id-1.9.3.183.8"><h2>Notes</h2><p>
 151    When a <code class="literal">FROM</code> clause is present, what essentially happens
 152    is that the target table is joined to the tables mentioned in the
 153    <em class="replaceable"><code>from_item</code></em> list, and each output row of the join
 154    represents an update operation for the target table.  When using
 155    <code class="literal">FROM</code> you should ensure that the join
 156    produces at most one output row for each row to be modified.  In
 157    other words, a target row shouldn't join to more than one row from
 158    the other table(s).  If it does, then only one of the join rows
 159    will be used to update the target row, but which one will be used
 160    is not readily predictable.
 161   </p><p>
 162    Because of this indeterminacy, referencing other tables only within
 163    sub-selects is safer, though often harder to read and slower than
 164    using a join.
 165   </p><p>
 166    In the case of a partitioned table, updating a row might cause it to no
 167    longer satisfy the partition constraint of the containing partition. In that
 168    case, if there is some other partition in the partition tree for which this
 169    row satisfies its partition constraint, then the row is moved to that
 170    partition. If there is no such partition, an error will occur.  Behind the
 171    scenes, the row movement is actually a <code class="command">DELETE</code> and
 172    <code class="command">INSERT</code> operation.
 173   </p><p>
 174    There is a possibility that a concurrent <code class="command">UPDATE</code> or
 175    <code class="command">DELETE</code> on the row being moved will get a serialization
 176    failure error.  Suppose session 1 is performing an <code class="command">UPDATE</code>
 177    on a partition key, and meanwhile a concurrent session 2 for which this
 178    row is visible performs an <code class="command">UPDATE</code> or
 179    <code class="command">DELETE</code> operation on this row.  In such case,
 180    session 2's <code class="command">UPDATE</code> or <code class="command">DELETE</code> will
 181    detect the row movement and raise a serialization failure error (which
 182    always returns with an SQLSTATE code '40001').  Applications may wish to
 183    retry the transaction if this occurs.  In the usual case where the table
 184    is not partitioned, or where there is no row movement, session 2 would
 185    have identified the newly updated row and carried out the
 186    <code class="command">UPDATE</code>/<code class="command">DELETE</code> on this new row
 187     version.
 188   </p><p>
 189    Note that while rows can be moved from local partitions to a foreign-table
 190    partition (provided the foreign data wrapper supports tuple routing), they
 191    cannot be moved from a foreign-table partition to another partition.
 192   </p><p>
 193    An attempt of moving a row from one partition to another will fail if a
 194    foreign key is found to directly reference an ancestor of the source
 195    partition that is not the same as the ancestor that's mentioned in the
 196    <code class="command">UPDATE</code> query.
 197   </p></div><div class="refsect1" id="id-1.9.3.183.9"><h2>Examples</h2><p>
 198    Change the word <code class="literal">Drama</code> to <code class="literal">Dramatic</code> in the
 199    column <code class="structfield">kind</code> of the table <code class="structname">films</code>:
 200
 201 </p><pre class="programlisting">
 202 UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
 203 </pre><p>
 204   </p><p>
 205    Adjust temperature entries and reset precipitation to its default
 206    value in one row of the table <code class="structname">weather</code>:
 207
 208 </p><pre class="programlisting">
 209 UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
 210   WHERE city = 'San Francisco' AND date = '2003-07-03';
 211 </pre><p>
 212   </p><p>
 213    Perform the same operation and return the updated entries, and the old
 214    precipitation value:
 215
 216 </p><pre class="programlisting">
 217 UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
 218   WHERE city = 'San Francisco' AND date = '2003-07-03'
 219   RETURNING temp_lo, temp_hi, prcp, old.prcp AS old_prcp;
 220 </pre><p>
 221   </p><p>
 222    Use the alternative column-list syntax to do the same update:
 223 </p><pre class="programlisting">
 224 UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
 225   WHERE city = 'San Francisco' AND date = '2003-07-03';
 226 </pre><p>
 227   </p><p>
 228    Increment the sales count of the salesperson who manages the
 229    account for Acme Corporation, using the <code class="literal">FROM</code>
 230    clause syntax:
 231 </p><pre class="programlisting">
 232 UPDATE employees SET sales_count = sales_count + 1 FROM accounts
 233   WHERE accounts.name = 'Acme Corporation'
 234   AND employees.id = accounts.sales_person;
 235 </pre><p>
 236   </p><p>
 237    Perform the same operation, using a sub-select in the
 238    <code class="literal">WHERE</code> clause:
 239 </p><pre class="programlisting">
 240 UPDATE employees SET sales_count = sales_count + 1 WHERE id =
 241   (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
 242 </pre><p>
 243   </p><p>
 244    Update contact names in an accounts table to match the currently assigned
 245    salespeople:
 246 </p><pre class="programlisting">
 247 UPDATE accounts SET (contact_first_name, contact_last_name) =
 248     (SELECT first_name, last_name FROM employees
 249      WHERE employees.id = accounts.sales_person);
 250 </pre><p>
 251    A similar result could be accomplished with a join:
 252 </p><pre class="programlisting">
 253 UPDATE accounts SET contact_first_name = first_name,
 254                     contact_last_name = last_name
 255   FROM employees WHERE employees.id = accounts.sales_person;
 256 </pre><p>
 257    However, the second query may give unexpected results
 258    if <code class="structname">employees</code>.<code class="structfield">id</code> is not a unique key, whereas
 259    the first query is guaranteed to raise an error if there are multiple
 260    <code class="structfield">id</code> matches.  Also, if there is no match for a particular
 261    <code class="structname">accounts</code>.<code class="structfield">sales_person</code> entry, the first query
 262    will set the corresponding name fields to NULL, whereas the second query
 263    will not update that row at all.
 264   </p><p>
 265    Update statistics in a summary table to match the current data:
 266 </p><pre class="programlisting">
 267 UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
 268     (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
 269      WHERE d.group_id = s.group_id);
 270 </pre><p>
 271   </p><p>
 272    Attempt to insert a new stock item along with the quantity of stock. If
 273    the item already exists, instead update the stock count of the existing
 274    item. To do this without failing the entire transaction, use savepoints:
 275 </p><pre class="programlisting">
 276 BEGIN;
 277 -- other operations
 278 SAVEPOINT sp1;
 279 INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
 280 -- Assume the above fails because of a unique key violation,
 281 -- so now we issue these commands:
 282 ROLLBACK TO sp1;
 283 UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
 284 -- continue with other operations, and eventually
 285 COMMIT;
 286 </pre><p>
 287   </p><p>
 288    Change the <code class="structfield">kind</code> column of the table
 289    <code class="structname">films</code> in the row on which the cursor
 290    <code class="literal">c_films</code> is currently positioned:
 291 </p><pre class="programlisting">
 292 UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
 293 </pre><p>
 294   </p><p id="UPDATE-LIMIT">
 295    Updates affecting many rows can have negative effects on system
 296    performance, such as table bloat, increased replica lag, and increased
 297    lock contention.  In such situations it can make sense to perform the
 298    operation in smaller batches, possibly with a <code class="command">VACUUM</code>
 299    operation on the table between batches.  While there is
 300    no <code class="literal">LIMIT</code> clause for <code class="command">UPDATE</code>, it is
 301    possible to get a similar effect through the use of
 302    a <a class="link" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Common Table Expression</a> and a
 303    self-join.  With the standard <span class="productname">PostgreSQL</span>
 304    table access method, a self-join on the system
 305    column <a class="link" href="ddl-system-columns.html#DDL-SYSTEM-COLUMNS-CTID">ctid</a> is very
 306    efficient:
 307 </p><pre class="programlisting">
 308 WITH exceeded_max_retries AS (
 309   SELECT w.ctid FROM work_item AS w
 310     WHERE w.status = 'active' AND w.num_retries &gt; 10
 311     ORDER BY w.retry_timestamp
 312     FOR UPDATE
 313     LIMIT 5000
 314 )
 315 UPDATE work_item SET status = 'failed'
 316   FROM exceeded_max_retries AS emr
 317   WHERE work_item.ctid = emr.ctid;
 318 </pre><p>
 319    This command will need to be repeated until no rows remain to be updated.
 320    Use of an <code class="literal">ORDER BY</code> clause allows the command to
 321    prioritize which rows will be updated; it can also prevent deadlock
 322    with other update operations if they use the same ordering.
 323    If lock contention is a concern, then <code class="literal">SKIP LOCKED</code>
 324    can be added to the <acronym class="acronym">CTE</acronym> to prevent multiple commands
 325    from updating the same row.  However, then a
 326    final <code class="command">UPDATE</code> without <code class="literal">SKIP LOCKED</code>
 327    or <code class="literal">LIMIT</code> will be needed to ensure that no matching
 328    rows were overlooked.
 329   </p></div><div class="refsect1" id="id-1.9.3.183.10"><h2>Compatibility</h2><p>
 330    This command conforms to the <acronym class="acronym">SQL</acronym> standard, except
 331    that the <code class="literal">FROM</code> and <code class="literal">RETURNING</code> clauses
 332    are <span class="productname">PostgreSQL</span> extensions, as is the ability
 333    to use <code class="literal">WITH</code> with <code class="command">UPDATE</code>.
 334   </p><p>
 335    Some other database systems offer a <code class="literal">FROM</code> option in which
 336    the target table is supposed to be listed again within <code class="literal">FROM</code>.
 337    That is not how <span class="productname">PostgreSQL</span> interprets
 338    <code class="literal">FROM</code>.  Be careful when porting applications that use this
 339    extension.
 340   </p><p>
 341    According to the standard, the source value for a parenthesized sub-list of
 342    target column names can be any row-valued expression yielding the correct
 343    number of columns.  <span class="productname">PostgreSQL</span> only allows the
 344    source value to be a <a class="link" href="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" title="4.2.13. Row Constructors">row
 345    constructor</a> or a sub-<code class="literal">SELECT</code>.  An individual column's
 346    updated value can be specified as <code class="literal">DEFAULT</code> in the
 347    row-constructor case, but not inside a sub-<code class="literal">SELECT</code>.
 348   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-unlisten.html" title="UNLISTEN">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-vacuum.html" title="VACUUM">Next</a></td></tr><tr><td width="40%" align="left" valign="top">UNLISTEN </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> VACUUM</td></tr></table></div></body></html>