| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.41 2006/09/16 00:30:20 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-UPDATE"> |
| <refmeta> |
| <refentrytitle id="SQL-UPDATE-TITLE">UPDATE</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>UPDATE</refname> |
| <refpurpose>update rows of a table</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-update"> |
| <primary>UPDATE</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ] |
| SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } | |
| ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...] |
| [ FROM <replaceable class="PARAMETER">fromlist</replaceable> ] |
| [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] |
| [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>UPDATE</command> 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 <literal>SET</literal> clause; |
| columns not explicitly modified retain their previous values. |
| </para> |
| |
| <para> |
| By default, <command>UPDATE</command> will update rows in the |
| specified table and all its subtables. If you wish to only update |
| the specific table mentioned, you must use the <literal>ONLY</> |
| clause. |
| </para> |
| |
| <para> |
| There 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 <literal>FROM</literal> clause. Which |
| technique is more appropriate depends on the specific |
| circumstances. |
| </para> |
| |
| <para> |
| The optional <literal>RETURNING</> clause causes <command>UPDATE</> |
| 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 <literal>FROM</literal>, can be computed. |
| The new (post-update) values of the table's columns are used. |
| The syntax of the <literal>RETURNING</> list is identical to that of the |
| output list of <command>SELECT</>. |
| </para> |
| |
| <para> |
| You must have the <literal>UPDATE</literal> privilege on the table |
| to update it, as well as the <literal>SELECT</literal> |
| privilege to any table whose values are read in the |
| <replaceable class="parameter">expressions</replaceable> or |
| <replaceable class="parameter">condition</replaceable>. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="PARAMETER">table</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of the table to update. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">alias</replaceable></term> |
| <listitem> |
| <para> |
| A substitute name for the target table. When an alias is |
| provided, it completely hides the actual name of the table. For |
| example, given <literal>UPDATE foo AS f</>, the remainder of the |
| <command>UPDATE</command> statement must refer to this table as |
| <literal>f</> not <literal>foo</>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">column</replaceable></term> |
| <listitem> |
| <para> |
| The name of a column in <replaceable |
| class="PARAMETER">table</replaceable>. |
| 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, |
| <literal>UPDATE tab SET tab.col = 1</> is invalid. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">expression</replaceable></term> |
| <listitem> |
| <para> |
| An expression to assign to the column. The expression may use the |
| old values of this and other columns in the table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>DEFAULT</literal></term> |
| <listitem> |
| <para> |
| Set the column to its default value (which will be NULL if no |
| specific default expression has been assigned to it). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">fromlist</replaceable></term> |
| <listitem> |
| <para> |
| A list of table expressions, allowing columns from other tables |
| to appear in the <literal>WHERE</> condition and the update |
| expressions. This is similar to the list of tables that can be |
| specified in the <xref linkend="sql-from" |
| endterm="sql-from-title"> of a <command>SELECT</command> |
| statement. Note that the target table must not appear in the |
| <replaceable>fromlist</>, unless you intend a self-join (in which |
| case it must appear with an alias in the <replaceable>fromlist</>). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">condition</replaceable></term> |
| <listitem> |
| <para> |
| An expression that returns a value of type <type>boolean</type>. |
| Only rows for which this expression returns <literal>true</> |
| will be updated. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">output_expression</replaceable></term> |
| <listitem> |
| <para> |
| An expression to be computed and returned by the <command>UPDATE</> |
| command after each row is updated. The expression may use any |
| column names of the <replaceable class="PARAMETER">table</replaceable> |
| or table(s) listed in <literal>FROM</>. |
| Write <literal>*</> to return all columns. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="PARAMETER">output_name</replaceable></term> |
| <listitem> |
| <para> |
| A name to use for a returned column. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Outputs</title> |
| |
| <para> |
| On successful completion, an <command>UPDATE</> command returns a command |
| tag of the form |
| <screen> |
| UPDATE <replaceable class="parameter">count</replaceable> |
| </screen> |
| The <replaceable class="parameter">count</replaceable> is the number |
| of rows updated. If <replaceable class="parameter">count</replaceable> is |
| 0, no rows matched the <replaceable |
| class="parameter">condition</replaceable> (this is not considered |
| an error). |
| </para> |
| |
| <para> |
| If the <command>UPDATE</> command contains a <literal>RETURNING</> |
| clause, the result will be similar to that of a <command>SELECT</> |
| statement containing the columns and values defined in the |
| <literal>RETURNING</> list, computed over the row(s) updated by the |
| command. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| When a <literal>FROM</> clause is present, what essentially happens |
| is that the target table is joined to the tables mentioned in the |
| <replaceable>fromlist</replaceable>, and each output row of the join |
| represents an update operation for the target table. When using |
| <literal>FROM</> 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. |
| </para> |
| |
| <para> |
| Because of this indeterminacy, referencing other tables only within |
| sub-selects is safer, though often harder to read and slower than |
| using a join. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| Change the word <literal>Drama</> to <literal>Dramatic</> in the |
| column <structfield>kind</> of the table <structname>films</structname>: |
| |
| <programlisting> |
| UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; |
| </programlisting> |
| </para> |
| |
| <para> |
| Adjust temperature entries and reset precipitation to its default |
| value in one row of the table <structname>weather</structname>: |
| |
| <programlisting> |
| UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT |
| WHERE city = 'San Francisco' AND date = '2003-07-03'; |
| </programlisting> |
| </para> |
| |
| <para> |
| Perform the same operation and return the updated entries: |
| |
| <programlisting> |
| UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT |
| WHERE city = 'San Francisco' AND date = '2003-07-03' |
| RETURNING temp_lo, temp_hi, prcp; |
| </programlisting> |
| </para> |
| |
| <para> |
| Use the alternative column-list syntax to do the same update: |
| <programlisting> |
| UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT) |
| WHERE city = 'San Francisco' AND date = '2003-07-03'; |
| </programlisting> |
| </para> |
| |
| <para> |
| Increment the sales count of the salesperson who manages the |
| account for Acme Corporation, using the <literal>FROM</literal> |
| clause syntax: |
| <programlisting> |
| UPDATE employees SET sales_count = sales_count + 1 FROM accounts |
| WHERE accounts.name = 'Acme Corporation' |
| AND employees.id = accounts.sales_person; |
| </programlisting> |
| </para> |
| |
| <para> |
| Perform the same operation, using a sub-select in the |
| <literal>WHERE</literal> clause: |
| <programlisting> |
| UPDATE employees SET sales_count = sales_count + 1 WHERE id = |
| (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); |
| </programlisting> |
| </para> |
| |
| <para> |
| Attempt 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. |
| <programlisting> |
| BEGIN; |
| -- other operations |
| SAVEPOINT sp1; |
| INSERT INTO wines VALUES('Chateau Lafite 2003', '24'); |
| -- Assume the above fails because of a unique key violation, |
| -- so now we issue these commands: |
| ROLLBACK TO sp1; |
| UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003'; |
| -- continue with other operations, and eventually |
| COMMIT; |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| This command conforms to the <acronym>SQL</acronym> standard, except |
| that the <literal>FROM</literal> and <literal>RETURNING</> clauses |
| are <productname>PostgreSQL</productname> extensions. |
| </para> |
| |
| <para> |
| According to the standard, the column-list syntax should allow a list |
| of columns to be assigned from a single row-valued expression, such |
| as a sub-select: |
| <programlisting> |
| UPDATE accounts SET (contact_last_name, contact_first_name) = |
| (SELECT last_name, first_name FROM salesmen |
| WHERE salesmen.id = accounts.sales_id); |
| </programlisting> |
| This is not currently implemented — the source must be a list |
| of independent expressions. |
| </para> |
| |
| <para> |
| Some other database systems offer a <literal>FROM</> option in which |
| the target table is supposed to be listed again within <literal>FROM</>. |
| That is not how <productname>PostgreSQL</productname> interprets |
| <literal>FROM</>. Be careful when porting applications that use this |
| extension. |
| </para> |
| </refsect1> |
| </refentry> |