|
<< Click to Display Table of Contents >> UPDATE Command |
  
|
|
<< Click to Display Table of Contents >> UPDATE Command |
|
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.
UPDATE [ MEMORY ] table_name SET column_name = { expression | query } [, ...] [ WHERE condition ]
or
UPDATE [ MEMORY ] table_name SET ( column_names_list ) = { row_query } [ WHERE condition ]
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.
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);
Added example:
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 )
;
__________________________