Update rows of a table.




<update statement: searched> ::=




UPDATE [ schema-name. ] table-name [ [ AS ] correlation-name ]

SET <set clause list>

[ WHERE <search condition> ]


<set clause list> ::=




column-name = <value expression> [ { , column-name = <value expression> }... ]





The UPDATE statement is used to modify the value of one ore more columns in a selection of rows in the table identified by table-name.





The current database is implicit if schema-name is not specified.


The SET clause is a comma-separated list of column assignments. The assigned value can be any valid value expression, including value functions, user-defined functions, scalar subqueries, case expressions, DEFAULT and NULL.


Parameters can be used to represent values supplied by the application.


The WHERE clause restricts the rows that are updated to those satisfying the search condition. If no WHERE clause is specified, all rows in the table are updated.


Tip: Using a prepared statement with parameters is more efficient than executing a sequence of individual UPDATE statements with explicit values.






The following example uses literals and value expressions to update studentID 211:


UPDATE students


studentName = firstName || ' ' || lastName,

address = '983 Park Avenue',

city = 'Boston',

state = ( SELECT state FROM students WHERE studentID = 473 ),

zip = '02169'

WHERE studentID = 211



The following example shows a generic UPDATE statement for the students table using parameters to update column values and identify the row:


UPDATE students


studentName = :firstName || ' ' || :lastName,

lastName = :lastName,

firstName = :firstName,

address = :address,

city = :city,

state = :state,

zip = :zip,

gender = :gender

WHERE studentID = :studentID






SQL:2003 standard


Core SQL