Product Documentation

Appearance

All platforms

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, or table.

Note: You cannot use UPDATE commands on tables in remote databases; see Cross-Database Queries.

Warning: Running EXPLAIN ANALYZE on an INSERT, UPDATE, or DELETE statement modifies the target table, without warning. To avoid this problem, you can run the EXPLAIN ANALYZE statement inside a transaction, then roll it back if needed.

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 to public.season:

premdb=# update public.season sn set sn.seasonid=100;
UPDATE 25

However, the following syntax returns an error because public.sn in the SET clause does not correspond to public.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 the CREATE 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, where sn 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 ...;

where col3 and col4 are columns in table t2. The SET 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 the UPDATE operation. This syntax is similar to the FROM clause in a SELECT statement. You can use a comma-delimited list of tables and join them in the WHERE clause, or you can use the JOIN...ON syntax in the FROM clause.

When an UPDATE is executed, the target table is joined to the other tables in the FROM clause. You can explicitly join the target table to FROM clause tables by providing a join condition in the WHERE 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 for t1, declared either after the initial UPDATE clause or in the FROM clause. The AS keyword is optional.

For more details, see Usage Notes on Aliases and Joins.

WHERE

Use this WHERE clause just as you would use the WHERE clause in a SELECT statement. Define a condition on either the target table or a table that is listed in the FROM clause. For example, you can join the target table to another listed table to qualify rows for the UPDATE operation.

Note: Table aliases are allowed in the WHERE clause. If the target table of the UPDATE is aliased, you must use the alias in the WHERE 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 the FROM clause. Use the WHERE clause to join the target table to other tables. For example, this statement explicitly joins target table t1 to table t2:
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 the FROM clause to join an aliased instance of the target table to other tables, but only if a self-join is intended. In the FROM clause, you can join tables with the JOIN...ON syntax. The first table to be joined is named directly after the FROM keyword. For example:
UPDATE t1 
SET c1 = t2.c1 
FROM t2 JOIN t3 on t2.c2 = t3.c2
WHERE t1.c2=t2.c2;

Note that t1 is joined to t2 in the WHERE clause. If you prefer, you can join all three tables explicitly in the WHERE 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 which t1 has no join condition to the other table (t2) but t1alias does:
UPDATE t1 
SET c1 = t2.c1 
FROM t1 t1alias 
JOIN t2 ON t1alias.c2 = t2.c2;

This is a common mistake that results in a cross-join between t1 and the result of the other join between t1alias and t2. 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 the FROM 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 ​

For example, update the 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

The following example uses a join to qualify rows for the 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

The following query checks the results of the last 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)

The following example joins the target table 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

The following example joins the following tables:

  • match (the target table)
  • m, an alias for match, used in a self-join
  • match_update (aliased as updt)

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
;

The following example is similar to the previous example but uses a 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
;