update man page on Scientific

Man page or keyword search:  
man Server   26626 pages
apropos Keyword Search (all sections)
Output format
Scientific logo
[printable version]

UPDATE(7)			 SQL Commands			     UPDATE(7)

NAME
       UPDATE - update rows of a table

SYNOPSIS
       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 ] [, ...] ]

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

       There are two ways to modify a table  using  information	 contained  in
       other  tables  in  the database: using sub-selects, or specifying addi‐
       tional 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
       condition.

PARAMETERS
       table  The  name	 (optionally schema-qualified) of the table to update.
	      If ONLY is specified before the table name,  matching  rows  are
	      updated  in  the	named  table  only.  If ONLY is not specified,
	      matching rows are also updated in any tables inheriting from the
	      named table. Optionally, * can be specified after the table name
	      to explicitly indicate that descendant tables are included.

       alias  A substitute name for the target table. When an  alias  is  pro‐
	      vided,  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.

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

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

       fromlist
	      A list of table expressions, allowing columns from other	tables
	      to  appear  in  the  WHERE condition and the update expressions.
	      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
	      fromlist).

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

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

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

       output_name
	      A name to use for a returned column.

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

       UPDATE count

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

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

NOTES
       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 tar‐
       get  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 tar‐
       get 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 tar‐
       get 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.

EXAMPLES
       Change  the  word  Drama	 to  Dramatic  in the column kind of the table
       films:

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

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

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

       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 accounts.name = 'Acme Corporation'
	 AND employees.id = 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	 save‐
       points:

       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;

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

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

COMPATIBILITY
       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 salesmen.id = accounts.sales_id);

       This  is not currently implemented — the source must be a list of inde‐
       pendent expressions.

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

SQL - Language Statements	  2013-10-08			     UPDATE(7)
[top]

List of man pages available for Scientific

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net