UPDATE

UPDATE — Update rows of a table

Syntax

UPDATE [schema.]table_name
[ join_type joined_item ON join_condition [...] ]
     SET column_name = expression [ , column_name = expression , ... ]
[ WHERE condition ]

Where joined_item is:

  { [schema.]object_name [ [AS] alias ]
    | ( select ) [ [AS] alias ]
    table_function }

Syntax using JOIN

Also see Example 5: Update a table using JOIN.

UPDATE [schema.]table_name
[join_type from_item ON join-condition [...]
    SET column_name = expression [ , column_name = expression , ... ]
[ WHERE condition ]

Description

The UPDATE statement is used to change rows in a table. The SET clause contains the list of columns in the table that will be updated by the UPDATE statement. Any number of columns in the table can be updated. Columns in the table that are not listed will retain their existing value, unless the values of the non listed columns are modified by a trigger.

The WHERE clause specifies the condition for which rows in the table will be updated by the UPDATE statement. This condition can be in the form of a value expression that returns a value of type boolean, where a true result is returned for each row selected, or in the form of a valid subquery SELECT. Refer to the SELECT statement for a further description of a valid subquery.

Note:

The user:

  • must have the UPDATE privilege on the table in order to modify it.

  • must have the SELECT privilege on all tables selected from in the WHERE clause.

  • needs SELECT privilege on any tables referenced in the join conditions of the FROM clause.

Parameters

table_name

Name (optionally schema-qualified) of an existing table or updatable view (see Using Updatable Views).

join_type

For the INNER and OUTER join types, a join condition (ON or USING) must be specified.

A JOIN clause combines two FROM items.

  • [INNER] JOIN

    INNER JOIN returns combined records from each FROM item, matched by the condition(s) in the ON clause. This join_type is a notational convenience, since they do nothing you could not do with FROM and WHERE.

  • LEFT [OUTER] JOIN

    LEFT OUTER JOIN returns combined records from each FROM item, matched by the condition(s) in the ON clause, plus one copy of each row in the left-hand FROM item for which there was no right-hand row that passed the JOIN condition. This left-hand row is extended to the full width of the joined result set by inserting null values for the right-hand columns.

    After the result set is returned from a JOIN, additional filtering of the combined result set can be done by defining conditions in a WHERE clause.

join-condition

The defined table whose records are updated must always appear in the leftmost side. Only the ON keyword is supported.

  • The SET clause containing the list of columns in the table that will be updated by the UPDATE statement must be set up so that the columns listed there do NOT come from any of the joined tables listed in "join-condition". Those columns can be used in the "expression" that is computed, but they must not be modified.

  • <table-to-update> can be a view. In that case, the table whose records are updated is the one in the leftmost side in the FROM clause of the view.
column_name

The name of a column in the table named by table_name.

expression

A valid expression to assign to column. This expression can be in the form of a constant value or in the form of a valid subquery SELECT statement. If expression is a SELECT statement, the statement must return only one row for each row in the table that it is updating. Otherwise, the UPDATE statement fails with error: "Subquery returns more than 1 row".

WHERE condition

This defines the filter criteria for determining which rows in the table will attempt to be updated. This condition can be in the form of a value expression that returns a value of type boolean, where a true result is returned for each row selected, or in the form of a valid subquery SELECT. Refer to the SELECT statement for a further description of a valid subquery

Examples

Example 1: Update all rows in a table that meet the value expression criteria.
UPDATE teams
   SET wins = wins + 1
  WHERE teamid IN ('COB','BOS','AND');
Example 2: Update all rows in a table setting the column values based on values in another table.
ALTER TABLE PLAYERS ADD total_games_played INTEGER;
UPDATE PLAYERS p
   SET p.total_games_played = (SELECT SUM(s.gamesplayed) FROM SCORING s
                                WHERE s.playerid = p.playerid);
Example 3: Update rows in a table setting the column values based on values in another table that meet some other criteria.
ALTER TABLE PLAYERS ADD winning_year_games INTEGER;
UPDATE PLAYERS p
   SET winning_year_games =
        (SELECT SUM(s.gamesplayed) FROM SCORING s WHERE
            s.playerid = p.playerid AND
            EXISTS (SELECT 'x' FROM teams t WHERE
                t.year = s.year AND
                t.teamid = s.teamid AND
                t.rank = 1) )
   WHERE p.LASTNHL = 2011;
Example 4: Update rows in a table but only if they exist in another table.
UPDATE PLAYERS p
   SET p.LASTNHL = p.LASTNHL + 1
 WHERE EXISTS (SELECT 'x' FROM SCORING s
                 WHERE s.playerid = p.playerid
                  AND s.year = 2012)
  AND p.LASTNHL = 2011;
Example 5: Update a table using JOIN.
UPDATE PLAYERS p
INNER JOIN SCORING s ON s.playerid = p.playerid
   SET  p.LASTNHL = p.LASTNHL + 1
WHERE p.LASTNHL = 2011
AND s.year = 2012;