<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.39 2006/09/02 20:34:47 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.40 2006/09/03 22:37:05 tgl Exp $

PostgreSQL documentation

-->

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

SET( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...]]

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

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

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

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

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 &mdash; 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</>.

UpdateStmt: UPDATE relation_expr_opt_alias

SETset_opt

SETset_clause_list

from_clause

where_clause

returning_clause

}

;

set_opt:

update_target_list{$$ =$1; }

| update_target_lists_list{$$ =$1; }

set_clause_list:

set_clause{$$ =$1; }

| set_clause_list',' set_clause{$$ = list_concat($1,$3); }

;

set_clause:

single_set_clause{$$ = list_make1($1); }

| multiple_set_clause{$$ =$1; }

;

single_set_clause:

set_target'=' ctext_expr

{

$$ =$1;

$$->val = (Node *)$3;

}

;

multiple_set_clause:

'(' set_target_list')''=' ctext_row

{

ListCell *col_cell;

ListCell *val_cell;

if (list_length($2) != list_length($5))

ereport(ERROR,

(errcode(ERRCODE_SYNTAX_ERROR),

errmsg("number of columns does not match number of values")));

forboth(col_cell, $2, val_cell, $5)

{

ResTarget *res_col = (ResTarget *)lfirst(col_cell);

Node *res_val = (Node *)lfirst(val_cell);

res_col->val = res_val;

}

$$ =$2;

}

;

set_target:

ColId opt_indirection

{

$$ = makeNode(ResTarget);

$$->name =$1;

$$->indirection =$2;

$$->val =NULL;/* upper production sets this*/

$$->location =@1;

}

;

set_target_list:

set_target{$$ = list_make1($1); }

| set_target_list',' set_target{$$ = lappend($1,$3); }

;

values_clause:

VALUES'(' values_list')'

VALUESctext_row

{

SelectStmt *n = makeNode(SelectStmt);

n->valuesLists = list_make1($3);

n->valuesLists = list_make1($2);

$$ = (Node *) n;

}

| values_clause',''(' values_list')'

| values_clause','ctext_row

{

SelectStmt *n = (SelectStmt *)$1;

n->valuesLists = lappend(n->valuesLists,$4);

n->valuesLists = lappend(n->valuesLists,$3);

$$ = (Node *) n;

}

;

values_list: values_item{$$ = list_make1($1); }

| values_list',' values_item{$$ = lappend($1,$3); }

;

values_item:

a_expr{$$ = (Node *)$1; }

| DEFAULT{$$ = (Node *) makeNode(SetToDefault); }

;

update_target_lists_list:

update_target_lists_el {$$ =$1; }

| update_target_lists_list',' update_target_lists_el {$$ = list_concat($1,$3); }

;

update_target_lists_el:

'(' update_col_list')''=''(' update_value_list')'

{

ListCell *col_cell;

ListCell *val_cell;

if (list_length($2) != list_length($6))

{

ereport(ERROR,

(errcode(ERRCODE_SYNTAX_ERROR),

errmsg("number of columns does not match to number of values")));

}

for (col_cell = list_head($2), val_cell = list_head($6);

col_cell !=NULL && val_cell !=NULL;

col_cell = lnext(col_cell), val_cell = lnext(val_cell))

{

ResTarget *res_col = (ResTarget *) lfirst(col_cell);

Node *res_val = (Node *) lfirst(val_cell);

res_col->val = res_val;

}

$$ =$2;

}

;

update_col_list:

update_col_list_el {$$ = list_make1($1); }

| update_col_list',' update_col_list_el {$$ = lappend($1,$3); }

;

update_col_list_el:

ColId opt_indirection

{

$$ = makeNode(ResTarget);

$$->name =$1;

$$->indirection =$2;

$$->val =NULL;

$$->location =@1;

}

;

update_value_list:

values_item {$$ = list_make1($1); }

| update_value_list',' values_item {$$ = lappend($1,$3); }

;

|/*EMPTY*/

;

ctext_expr:

a_expr{$$ = (Node *)$1; }

| DEFAULT{$$ = (Node *) makeNode(SetToDefault); }

;

ctext_expr_list:

ctext_expr{$$ = list_make1($1); }

| ctext_expr_list',' ctext_expr{$$ = lappend($1,$3); }

;

ctext_row:'(' ctext_expr_list')'{$$ =$2; }

;

}

;

update_target_list:

update_target_el {$$ = list_make1($1); }

| update_target_list',' update_target_el {$$ = lappend($1,$3); }

;

update_target_el:

ColId opt_indirection'=' a_expr

{

$$ = makeNode(ResTarget);

$$->name =$1;

$$->indirection =$2;

$$->val = (Node *)$4;

$$->location =@1;

}

| ColId opt_indirection'=' DEFAULT

{

$$ = makeNode(ResTarget);

$$->name =$1;

$$->indirection =$2;

$$->val = (Node *) makeNode(SetToDefault);

$$->location =@1;

}

;