UPDATE
Update rows or columns in a table by setting values based on a query or a condition.
[ WITH name AS (subquery) [, ...] ]
UPDATE table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
( column_name ) = ( subquery )
} [, ...]
[ FROM list ]
[ WHERE condition ]
- WITH
- See WITH Clause for syntax details. You can
reference a WITH subquery in the FROM clause of the
UPDATE
statement. - table_name
- The target table for the
UPDATE
statement. You can specify the table as:database.schema.table
,schema.table
, ortable
.Note: You cannot useUPDATE
commands on tables in remote databases; see Cross-Database Queries. You cannot useUPDATE
commands on tables in hot-standby databases; see HOT_STANDBY and READONLY Modes.Warning: RunningEXPLAIN ANALYZE
on anINSERT
,UPDATE
, orDELETE
statement modifies the target table, without warning. To avoid this problem, you can run theEXPLAIN ANALYZE
statement inside a transaction, then roll it back if needed. See EXPLAIN ANALYZE for a DELETE. - AS alias
- An optional alias for the table. If you specify an alias, the alias corresponds to the exact specification of the table. For example, this syntax is valid, where
sn
corresponds topublic.season
:premdb=# update public.season sn set sn.seasonid=100; UPDATE 25
However, the following syntax returns an error becausepublic.sn
in theSET
clause does not correspond topublic.season
:premdb=# update public.season sn set public.sn.seasonid=100; ERROR: Table "public"."sn" is not the UPDATE relation("public"."season") or doesn't exist LINE 1: update public.season sn set public.sn.seasonid=100;
- SET column_name
- Set one or more columns in the target table equal to specific values, or to
DEFAULT
values (as defined in theCREATE TABLE
statement). The column must exist in the target table and must be unambiguous.A column name can be specified in any of the following ways:column
For example:
seasonid
table.column
For example:
season.seasonid
schema.table.column
For example:
public.season.seasonid
database.schema.table.column
For example:
premdb.public.season.seasonid
alias.column
For example:
sn.seasonid
, wheresn
is defined as an alias for the target table
- subquery
- A single column may be set to equal the results of a subquery. For example:
premdb=# update season set seasonid=(select max(seasonid) from match); UPDATE 25
The following syntax is not supported:update t1 set (col1, col2) = (select col3, col4 from t2 ...);
However, you can use the following syntax to set multiple column values at once:update t1 set (col1, col2) = (col3, col4) from t2 ...;
wherecol3
andcol4
are columns in tablet2
. TheSET
values can be expressions or constants. For example:premdb=# create table t1(col1 int, col2 int); CREATE TABLE premdb=# create table t2(col3 int, col4 int); CREATE TABLE premdb=# insert into t1 values(1,10); INSERT 0 1 premdb=# insert into t2 values(2,20); INSERT 0 1 premdb=# update t1 set (col1, col2)=(col3, col4) from t2; UPDATE 1 premdb=# select * from t1; col1 | col2 ------+------ 2 | 20 (1 row)
- FROM
- Name one or more tables or table references, other than the target table. These
other tables may then be joined and/or referenced in
WHERE
clause conditions for theUPDATE
operation. This syntax is similar to theFROM
clause in aSELECT
statement. You can use a comma-delimited list of tables and join them in theWHERE
clause, or you can use theJOIN...ON
syntax in theFROM
clause.When anUPDATE
is executed, the target table is joined to the other tables in theFROM
clause. You can explicitly join the target table toFROM
clause tables by providing a join condition in theWHERE
clause. For example:UPDATE t1...FROM t2 WHERE t1.x=t2.x
You cannot write:UPDATE t1...JOIN t2 on t1.x=t2.x
Note: Do not list the target table for the UPDATE in the FROM clause unless you intend to specify a self-join. For self-joins, the target table must have an alias that specifies the self-joining instance of the table.For example, both of these statements are valid self-joins:UPDATE t1 SET c1=10 FROM t1 AS t1a WHERE t1a.c2=t1.c2; UPDATE t1 AS t1a SET c1=10 FROM t1 WHERE t1a.c2=t1.c2;
In both cases,
t1a
is an alias fort1
, declared either after the initialUPDATE
clause or in theFROM
clause. TheAS
keyword is optional.For more details, see Usage Notes on Aliases and Joins.
- WHERE
- Use this
WHERE
clause just as you would use theWHERE
clause in aSELECT
statement. Define a condition on either the target table or a table that is listed in theFROM
clause. For example, you can join the target table to another listed table to qualify rows for theUPDATE
operation.Note: Table aliases are allowed in theWHERE
clause. If the target table of theUPDATE
is aliased, you must use the alias in theWHERE
clause. For example:premdb=# update match as m1 set ftscore='0-0' where match.htid=0; ERROR: invalid reference to FROM-clause entry for table "match" LINE 1: update match as m1 set ftscore='0-0' where match.htid=0; ^ HINT: Perhaps you meant to reference the table alias "m1".
Usage Notes on Aliases and Joins
- You can use a table alias for the target table name.
- You can reference a target table alias in the
SET
clause, as a prefix for the column name. - If the target table is aliased, you must use the alias (and not the base table name) in
WHERE
clause references to the target table. - You can also alias any tables that are listed in the
FROM
clause or used in a subquery (including other instances of the target table used for self-joins). - The
FROM
clause is only required when the target table is joined to other tables or to itself (a self-join). Do not list the target table for the UPDATE in the FROM clause unless you intend to specify a self-join. For self-joins, the target table must have an alias that specifies the self-joining instance of the table. - You can specify join conditions in the
WHERE
clause or theFROM
clause. Use theWHERE
clause to join the target table to other tables. For example, this statement explicitly joins target tablet1
to tablet2
:UPDATE t1 SET c1 = t2.c1 FROM t2 WHERE t2.c2 = t1.c2;
- You can specify join conditions in the
FROM
clause to explicitly join additional tables to each other. You can also use theFROM
clause to join an aliased instance of the target table to other tables, but only if a self-join is intended. In theFROM
clause, you can join tables with theJOIN...ON
syntax. The first table to be joined is named directly after theFROM
keyword. For example:UPDATE t1 SET c1 = t2.c1 FROM t2 JOIN t3 on t2.c2 = t3.c2 WHERE t1.c2=t2.c2;
Note thatt1
is joined tot2
in theWHERE
clause. If you prefer, you can join all three tables explicitly in theWHERE
clause:UPDATE t1 SET c1=t2.c1 FROM t2, t3 WHERE t1.c2=t2.c2 AND t2.c2 = t3.c2;
- Pay special attention to the complete syntax of the
UPDATE
statement when you are joining tables. If you do not specify a full set of join conditions between all of the named tables, the query plan may generate a very costly cross-join. For example, consider this statement, in whicht1
has no join condition to the other table (t2
) butt1alias
does:
This is a common mistake that results in a cross-join betweenUPDATE t1 SET c1 = t2.c1 FROM t1 t1alias JOIN t2 ON t1alias.c2 = t2.c2;
t1
and the result of the other join betweent1alias
andt2
. This mistake may derive simply from faulty join syntax, or it may derive from a misconception that the target table needs to be named (and aliased) in theFROM
clause. It does not need to be named unless a self-join is intended. - Each output row of a join of the target table to another table results in an update on
the target table. Make sure that the join produces only one output row (or none) for each
row to be modified. A target table row should not join to multiple rows from the other
tables. If it does, the following error is returned:
ERROR: Attempt to update a target row multiple times
Examples
team
table, aliased as t
, and set
four columns to the same value based on a condition:
premdb=# update team t
set (t.name, t.nickname, t.city, t.stadium) = ('TBD','TBD','TBD','TBD')
where t.name is null;
UPDATE 3
UPDATE
statement. A stadium
column is added to the match
table.
The UPDATE
statement joins the match
and
team
tables to update the match.stadium
column with the
same values found in the team.stadium
column. An alias m
is used for the target table and referenced in the WHERE
clause.
premdb=# alter table match add column stadium varchar(50);
ALTER TABLE
premdb=# update match m set m.stadium=t.stadium from team t where t.htid=m.htid;
UPDATE 8606
UPDATE
(for one
htid
and atid
combination):premdb=# select * from match where htid=42 and atid=89 order by seasonid;
seasonid | matchday | htid | atid | ftscore | htscore | stadium
----------+---------------------+------+------+---------+---------+-----------------
20 | 2012-04-01 00:00:00 | 42 | 89 | 3-1 | 1-0 | White Hart Lane
21 | 2012-12-16 00:00:00 | 42 | 89 | 1-0 | 0-0 | White Hart Lane
22 | 2013-08-25 00:00:00 | 42 | 89 | 1-0 | 0-0 | White Hart Lane
(3 rows)
match
to the
season
table. The SET
clause uses an expression based on
the matchday
column. Rows are qualified for the update via the join
condition and an additional
predicate:premdb=# update match m set m.matchday=m.matchday+interval '15 hours'
from season s
where m.seasonid=s.seasonid and s.seasonid=1;
UPDATE 462
match
(the target table)m
, an alias formatch
, used in a self-joinmatch_update
(aliased asupdt
)
All three tables are explicitly joined, using the FROM
and
WHERE
clauses for the join conditions. The target table match is joined
in the WHERE
clause, and the other tables are joined in the
FROM
clause.
UPDATE match
SET ftscore = updt.ftscore, htscore = updt.htscore
FROM match_update updt
JOIN match m ON m.matchday = updt.matchday
AND m.htid = updt.htid
AND m.atid = updt.atid
WHERE match.matchday = updt.matchday
AND match.htid = updt.htid
AND match.atid = updt.atid
AND m.ftscore is null;
The following example is similar to the previous example but uses a subquery:
UPDATE match
SET
ftscore = updt.ftscore
, htscore = updt.htscore
FROM
(
SELECT
m.matchday
, m.htid
, m.atid
, u.ftscore
, u.htscore
FROM
match m
JOIN match_update u ON
m.matchday = updt.matchday
AND m.htid = updt.htid
AND m.atid = updt.atid
WHERE
m.ftscore is null
) updt
WHERE
match.matchday = updt.matchday
AND match.htid = updt.htid
AND match.atid = updt.atid
;
WITH
clause:
WITH updt AS
( SELECT
m.matchday
, m.htid
, m.atid
, u.ftscore
, u.htscore
FROM
match m
JOIN match_update u ON
m.matchday = updt.matchday
AND m.htid = updt.htid
AND m.atid = updt.atid
WHERE
m.ftscore is null
)
UPDATE
match
SET
ftscore = updt.ftscore
, htscore = updt.htscore
FROM
updt
WHERE
match.matchday = updt.matchday
AND match.htid = updt.htid
AND match.atid = updt.atid
;