UPDATE()			 SQL Commands			      UPDATE()



NAME
       UPDATE - update rows of a table


SYNOPSIS
       UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
	   [ FROM fromlist ]
	   [ WHERE condition ]


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.

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

       You  must  have the UPDATE privilege on the table to update it, as well
       as the SELECT privilege to any table  whose  values  are	 read  in  the
       expressions or condition.

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

       column The name of a column in table.  The column name can be qualified
	      with a subfield name or array subscript, if needed.

       expression
	      An expression to assign to the column. The  expression  may  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.

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

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


       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;


COMPATIBILITY
       This command conforms to the SQL standard, except that the FROM	clause
       is a PostgreSQL extension.

       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	  2010-12-14			      UPDATE()