UPDATE Command

Previous  Top  Next

An UPDATE statement is used to directly change or modify the values stored in one or more fields in a specified record in a single table.

Syntax

UPDATE [ MEMORY ] table_name SET column_name = { expression | query } [, ...] [ WHERE condition ]
or
UPDATE [ MEMORY ] table_name SET ( column_names_list ) = { row_query } [ WHERE condition ]


Description

UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.
You can use sub-selects to modify a table using information contained in other tables in the database.

MEMORY

If MEMORY keyword is specified before the table_name then an in-memory table is referenced, not a disk one.

table_name

The name of an existing table.

column _name

The name of a column in a table.

expression

An expression or a value to assign to a column.

query

A scalar query (SELECT statement) that supplies the single value to be assigned. Refer to the SELECT command for a description of the syntax.

condition

An expression that returns a value of type boolean. Only rows for which this expression returns true will be updated.

column_names_list

A list of a column names in a table.

row_query

A row query (SELECT statement) that supplies the single or multiple values to be assigned to the listed columns. Refer to the SELECT command for a description of the syntax.

Examples:

UPDATE MyTable SET val2 = 'Many' WHERE val1 > 2;

UPDATE Events e SET Ticket_price = (SELECT Capacity FROM Venues WHERE VenueNo=e.VenueNo)*2 WHERE Ticket_price <= 5

UPDATE Orders SET (ShipToAddr1,ShipToAddr2) = (SELECT Addr1, Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)