UPDATE

UPDATE — Update rows of a table

Syntax

UPDATE [schema.]table_name
   SET column_name = expression [ , column_name = expression , ... ]
[ WHERE condition ]

Syntax using JOIN

Also see Example 5: Update a table using JOIN.

UPDATE [schema.]table_name
[<join-specification>]
    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-specification>

The defined table whose records are updated must always appear in the leftmost side. The types of join products supported by these statements are the same as those supported by the SELECT statement.

Notes

  • 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-specification". 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;