Man Linux: Main Page and Category List


       UPDATE - update rows of a table


       UPDATE [ ONLY ] table [ [ AS ] alias ]
           SET { column = { expression | DEFAULT } |
                 ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
           [ FROM fromlist ]
           [ WHERE condition | WHERE CURRENT OF cursor_name ]
           [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]


       UPDATE  changes  the  values  of the specified columns in all rows that
       satisfy the  condition.  Only  the  columns  to  be  modified  need  be
       mentioned  in  the  SET  clause; columns not explicitly modified retain
       their previous values.

       By default, UPDATE will update rows in the specified table and all  its
       subtables. If you wish to only update the specific table mentioned, you
       must use the ONLY clause.

       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  FROM  clause.  Which  technique  is   more
       appropriate depends on the specific circumstances.

       The  optional  RETURNING  clause  causes  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 FROM, can
       be computed.  The new (post-update) values of the table’s  columns  are
       used.   The  syntax  of  the RETURNING list is identical to that of the
       output list of SELECT.

       You must have the UPDATE privilege on the table, or  at  least  on  the
       column(s) that are listed to be updated.  You must also have the SELECT
       privilege on any column whose values are read  in  the  expressions  or


       table  The name (optionally schema-qualified) of the table to update.

       alias  A  substitute  name  for  the  target  table.  When  an alias is
              provided, it completely hides the actual name of the table.  For
              example,  given  UPDATE  foo  AS  f, the remainder of the UPDATE
              statement must refer to this table as f not foo.

       column The name of a column in table.  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, UPDATE tab SET tab.col = 1 is invalid.

              An  expression  to  assign to the column. The expression can use
              the old values of this and other columns in the table.

              Set the column to its default value (which will be  NULL  if  no
              specific default expression has been assigned to it).

              A  list of table expressions, allowing columns from other tables
              to appear in the WHERE condition  and  the  update  expressions.
              This  is  similar to the list of tables that can be specified in
              the FROM Clause [select(7)] of a SELECT statement. Note that the
              target  table must not appear in the fromlist, unless you intend
              a self-join (in which case it must appear with an alias  in  the

              An  expression  that returns a value of type boolean.  Only rows
              for which this expression returns true will be updated.

              The name of the cursor to use in a WHERE CURRENT  OF  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  UPDATE’s
              target  table.   Note  that WHERE CURRENT OF cannot be specified
              together with a Boolean condition. See DECLARE [declare(7)]  for
              more information about using cursors with WHERE CURRENT OF.

              An  expression to be computed and returned by the UPDATE command
              after each row is updated. The expression  can  use  any  column
              names  of  the  table  or  table(s)  listed in FROM.  Write * to
              return all columns.

              A name to use for a returned column.


       On successful completion, an UPDATE command returns a  command  tag  of
       the form

       UPDATE count

       The count is the number of rows updated. If count is 0, no rows matched
       the condition (this is not considered an error).

       If the UPDATE command contains a RETURNING clause, the result  will  be
       similar to that of a SELECT statement containing the columns and values
       defined in the RETURNING list, computed over the row(s) updated by  the


       When  a  FROM  clause  is present, what essentially happens is that the
       target table is joined to the tables mentioned  in  the  fromlist,  and
       each  output  row  of  the  join represents an update operation for the
       target table. When using 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.

       Because of this indeterminacy, referencing  other  tables  only  within
       sub-selects is safer, though often harder to read and slower than using
       a join.


       Change the word Drama to Dramatic in  the  column  kind  of  the  table

       UPDATE films SET kind = ’Dramatic’ WHERE kind = ’Drama’;

       Adjust temperature entries and reset precipitation to its default value
       in one row of the table weather:

       UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
         WHERE city = ’San Francisco’ AND date = ’2003-07-03’;

       Perform the same operation and return the updated entries:

       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;

       Use the alternative column-list syntax to do the same update:

       UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
         WHERE city = ’San Francisco’ AND date = ’2003-07-03’;

       Increment the sales count of the salesperson who  manages  the  account
       for Acme Corporation, using the FROM clause syntax:

       UPDATE employees SET sales_count = sales_count + 1 FROM accounts
         WHERE = ’Acme Corporation’
         AND = accounts.sales_person;

       Perform the same operation, using a sub-select in the WHERE clause:

       UPDATE employees SET sales_count = sales_count + 1 WHERE id =
         (SELECT sales_person FROM accounts WHERE name = ’Acme Corporation’);

       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

       -- 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

       Change the kind column of the table films  in  the  row  on  which  the
       cursor c_films is currently positioned:

       UPDATE films SET kind = ’Dramatic’ WHERE CURRENT OF c_films;


       This  command  conforms  to  the SQL standard, except that the FROM and
       RETURNING clauses are PostgreSQL extensions.

       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:

       UPDATE accounts SET (contact_last_name, contact_first_name) =
           (SELECT last_name, first_name FROM salesmen
            WHERE = accounts.sales_id);

       This is not currently implemented —  the  source  must  be  a  list  of
       independent expressions.

       Some  other  database  systems  offer a FROM option in which the target
       table is supposed to be listed again within  FROM.   That  is  not  how
       PostgreSQL  interprets  FROM. Be careful when porting applications that
       use this extension.