Update rows of a table.

 

Syntax

 

<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> }... ]

 

 

Usage

 

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.

 

 

Notes

 

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.

 

 

Examples

 

1)

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



 


UPDATE students


SET




studentName = firstName || ' ' || lastName,


address = '983 Park Avenue',


city = 'Boston',


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


zip = '02169'




WHERE studentID = 211



 

2)

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



 


UPDATE students


SET




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


lastName = :lastName,


firstName = :firstName,


address = :address,


city = :city,


state = :state,


zip = :zip,


gender = :gender




WHERE studentID = :studentID



 

 


 

Conformance

 

SQL:2003 standard

-

Core SQL