UPDATE Statement

Changes values in columns in a specified table or query.




UPDATE table SET column = {expression | DEFAULT} [, column = {expression | DEFAULT} ...] [WHERE ...]


The UPDATE statement has these parts:





The name of the table or query containing the data we want to modify.


The name of the column we want to modify.


An expression with which to replace the old value. Can be a query that returns a single value.




The UPDATE statement is especially useful when we want to change many records or when the records that we want to change are in multiple tables. If the records we want to change are in multiple tables, we create a SELECT query joining these tables together with UNION, and then use the UPDATE query against the SELECT.


Use the DEFAULT keyword to set the column to its default value.


If the result of the expression on the right side of the assignment operator is NULL, the value in the respective column is left unchanged.


We can change several columns at the same time. The following example increases the unit price by 10 percent and the reorder level by 50 for products with low number of units in stock:


UPDATE [Products] SET [Unit Price] = [Unit Price] * 1.1,

[Reorder Level] = [Reorder Level] + 50

WHERE [Units In Stock] <= 3;


The UPDATE statement is an action query and cannot be used from within the Manifold ODBC driver.




After we update records using an UPDATE query, we can't undo the operation. If we want to know which records were updated, we first examine the results of a SELECT query that uses the same criteria, and then run the update query.


Maintain backup copies of data at all times. If we update the wrong records, we can retrieve them from our backup copies.




This example changes values in the "Reports To" column to 5 for all employee records that currently have "Reports To" values of 2:


UPDATE [Employees] SET [Reports To] = 5 WHERE [Reports To] = 2;


Suppose you have a table named "Places" with "Name" and "Population" columns, and a table named "Births" with "Place," and "Births" columns, and you want to add "Births" from the "Births" table into the relevant record in the "Places" table.


To do this, use the following query:


UPDATE [Places] SET [Population] = [Population] + (SELECT Sum([Births]) FROM [Births] WHERE [Name] = [Place]);



Tech Tip


UPDATE queries support use of table name aliases.