1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
16
HREF="sql-commands.html"><LINK
19
HREF="sql-unlisten.html"><LINK
22
HREF="sql-vacuum.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
57
HREF="sql-unlisten.html"
67
HREF="sql-unlisten.html"
81
HREF="sql-vacuum.html"
90
HREF="sql-vacuum.html"
111
>UPDATE -- update rows of a table</DIV
113
CLASS="REFSYNOPSISDIV"
121
>[ WITH [ RECURSIVE ] <TT
154
> [, ...] ) = ( { <TT
159
> | DEFAULT } [, ...] ) } [, ...]
171
> | WHERE CURRENT OF <TT
180
>output_expression</I
200
> changes the values of the specified
201
columns in all rows that satisfy the condition. Only the columns to
202
be modified need be mentioned in the <TT
206
columns not explicitly modified retain their previous values.
212
> will update rows in the
213
specified table and all its subtables. If you wish to only update
214
the specific table mentioned, you must use the <TT
221
> There are two ways to modify a table using information contained in
222
other tables in the database: using sub-selects, or specifying
223
additional tables in the <TT
227
technique is more appropriate depends on the specific
238
to compute and return value(s) based on each row actually updated.
239
Any expression using the table's columns, and/or columns of other
240
tables mentioned in <TT
244
The new (post-update) values of the table's columns are used.
245
The syntax of the <TT
248
> list is identical to that of the
255
> You must have the <TT
258
> privilege on the table,
259
or at least on the column(s) that are listed to be updated.
260
You must also have the <TT
264
privilege on any column whose values are read in the
303
> clause allows you to specify one or more
304
subqueries that can be referenced by name in the <TT
309
HREF="queries-with.html"
312
HREF="sql-select.html"
327
> The name (optionally schema-qualified) of the table to update.
339
> A substitute name for the target table. When an alias is
340
provided, it completely hides the actual name of the table. For
344
>, the remainder of the
348
> statement must refer to this table as
367
> The name of a column in <TT
373
The column name can be qualified with a subfield name or array
374
subscript, if needed. Do not include the table's name in the
375
specification of a target column — for example,
378
>UPDATE tab SET tab.col = 1</TT
391
> An expression to assign to the column. The expression can use the
392
old values of this and other columns in the table.
402
> Set the column to its default value (which will be NULL if no
403
specific default expression has been assigned to it).
415
> A list of table expressions, allowing columns from other tables
419
> condition and the update
420
expressions. This is similar to the list of tables that can be
422
HREF="sql-select.html#SQL-FROM"
432
statement. Note that the target table must not appear in the
438
>, unless you intend a self-join (in which
439
case it must appear with an alias in the <TT
456
> An expression that returns a value of type <TT
460
Only rows for which this expression returns <TT
476
> The name of the cursor to use in a <TT
478
>WHERE CURRENT OF</TT
480
condition. The row to be updated is the one most recently fetched
481
from this cursor. The cursor must be a non-grouping
488
>WHERE CURRENT OF</TT
490
specified together with a Boolean condition. See
492
HREF="sql-declare.html"
495
for more information about using cursors with
498
>WHERE CURRENT OF</TT
506
>output_expression</I
511
> An expression to be computed and returned by the <TT
515
command after each row is updated. The expression can use any
516
column names of the <TT
522
or table(s) listed in <TT
529
> to return all columns.
541
> A name to use for a returned column.
555
> On successful completion, an <TT
558
> command returns a command
575
of rows updated. If <TT
581
0, no rows matched the <TT
586
> (this is not considered
593
> command contains a <TT
597
clause, the result will be similar to that of a <TT
601
statement containing the columns and values defined in the
605
> list, computed over the row(s) updated by the
620
> clause is present, what essentially happens
621
is that the target table is joined to the tables mentioned in the
627
>, and each output row of the join
628
represents an update operation for the target table. When using
632
> you should ensure that the join
633
produces at most one output row for each row to be modified. In
634
other words, a target row shouldn't join to more than one row from
635
the other table(s). If it does, then only one of the join rows
636
will be used to update the target row, but which one will be used
637
is not readily predictable.
640
> Because of this indeterminacy, referencing other tables only within
641
sub-selects is safer, though often harder to read and slower than
653
> Change the word <TT
669
CLASS="PROGRAMLISTING"
670
>UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';</PRE
674
> Adjust temperature entries and reset precipitation to its default
675
value in one row of the table <TT
681
CLASS="PROGRAMLISTING"
682
>UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
683
WHERE city = 'San Francisco' AND date = '2003-07-03';</PRE
687
> Perform the same operation and return the updated entries:
690
CLASS="PROGRAMLISTING"
691
>UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
692
WHERE city = 'San Francisco' AND date = '2003-07-03'
693
RETURNING temp_lo, temp_hi, prcp;</PRE
697
> Use the alternative column-list syntax to do the same update:
699
CLASS="PROGRAMLISTING"
700
>UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
701
WHERE city = 'San Francisco' AND date = '2003-07-03';</PRE
705
> Increment the sales count of the salesperson who manages the
706
account for Acme Corporation, using the <TT
712
CLASS="PROGRAMLISTING"
713
>UPDATE employees SET sales_count = sales_count + 1 FROM accounts
714
WHERE accounts.name = 'Acme Corporation'
715
AND employees.id = accounts.sales_person;</PRE
719
> Perform the same operation, using a sub-select in the
725
CLASS="PROGRAMLISTING"
726
>UPDATE employees SET sales_count = sales_count + 1 WHERE id =
727
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');</PRE
731
> Attempt to insert a new stock item along with the quantity of stock. If
732
the item already exists, instead update the stock count of the existing
733
item. To do this without failing the entire transaction, use savepoints:
735
CLASS="PROGRAMLISTING"
739
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
740
-- Assume the above fails because of a unique key violation,
741
-- so now we issue these commands:
743
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
744
-- continue with other operations, and eventually
752
> column of the table
756
> in the row on which the cursor
760
> is currently positioned:
762
CLASS="PROGRAMLISTING"
763
>UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;</PRE
775
> This command conforms to the <ACRONYM
789
> extensions, as is the ability
799
> According to the standard, the column-list syntax should allow a list
800
of columns to be assigned from a single row-valued expression, such
803
CLASS="PROGRAMLISTING"
804
>UPDATE accounts SET (contact_last_name, contact_first_name) =
805
(SELECT last_name, first_name FROM salesmen
806
WHERE salesmen.id = accounts.sales_id);</PRE
808
This is not currently implemented — the source must be a list
809
of independent expressions.
812
> Some other database systems offer a <TT
816
the target table is supposed to be listed again within <TT
820
That is not how <SPAN
827
>. Be careful when porting applications that use this
836
SUMMARY="Footer navigation table"
847
HREF="sql-unlisten.html"
865
HREF="sql-vacuum.html"
881
HREF="sql-commands.html"
b'\\ No newline at end of file'