UPDATE
— Update rows of a table
UPDATE [schema
.]table_name
SETcolumn_name
=expression
[ ,column_name
=expression
, ... ] [ WHEREcondition
]
Also see Example 5: Update a table using JOIN.
UPDATE [schema.]table_name [<join-specification>] SET column_name = expression [ , column_name = expression , ... ] [ WHERE condition ]
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.
The user:
UPDATE
privilege on the table in order to modify it. SELECT
privilege on all tables selected from in the WHERE
clause.
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
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
UPDATE teams
SET wins = wins + 1
WHERE teamid IN ('COB','BOS','AND');
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);
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;
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;
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