Open topic with navigation

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 
   SET column_name = expression [ , column_name = expression , ... ]
[ WHERE condition ]
       [<join-specification>]

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:

Parameters

Closedtable_name

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

Closedcolumn_name

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

Closedexpression

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

ClosedWHERE 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

ClosedJOIN condition

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.

Examples

ClosedExample 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');
ClosedExample 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);
ClosedExample 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;
ClosedExample 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;
ClosedExample 5: Update a table using JOIN. 
UPDATE PLAYERS p INNER 
   SET  p.LASTNHL = p.LASTNHL + 1 
 WHERE p.LASTNHL = 2011
    JOIN SCORING s ON s.playerid = p.playerid AND s.year = 2012