UPDATE Command

<< Click to Display Table of Contents >>

Navigation:  SQL Reference > SQL Commands >

UPDATE Command

Previous pageReturn to chapter overviewNext page

 

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);

 

Updating from another table based on condition on one or more columns:

UPDATE TableA

SET Val =

(SELECT TableB.ValB

 FROM TableB

 WHERE TableB.ItemB = TableA.Item AND TableB.ItemBDate = TableA.ItemDate )

--Prevents NULL'ing fields in non-matching records:

WHERE EXISTS

 (SELECT TableB.ItemB

  FROM TableB

  WHERE TableB.ItemB = TableA.Item AND TableB.ItemBDate = TableA.ItemDate )

;

__________________________
Absolute SQL Reference - 06 February 2021, Niels Knabe