How to: Create Update Queries (Visual Database Tools)
You can change the contents of multiple rows in one operation by using an Update query. For example, in a titles table you can use an Update query to add 10% to the price of all books for a particular publisher.
When you create an Update query, you specify:
- The table to update.
- The columns whose contents you want to update.
- The value or expression to use to update the individual columns.
- Search conditions to define the rows you want to update.
For example, the following query updates the titles table by adding 10% to the price of all titles for one publisher:
UPDATE titles
SET price = price * 1.1
WHERE (pub_id = '0766')
Warning
You cannot undo the action of executing an Update query. As a precaution, back up your data before executing the query.
To create an Update query
Add the table you want to update to the Diagram pane.
From the Query Designer menu point to Change Type, and then click Update.
Note
If more than one table is displayed in the Diagram pane when you start the Update query, the Query and View Designer displays the Choose Target Table for Insert Values Dialog Box to prompt you for the name of the table to update.
In the Diagram pane, click the check box for each column for which you want to supply new values. Those columns will show in the Criteria pane. Columns will be updated only if you add them to the query.
In the New Value column of the Criteria pane, enter the update value for the column. You can enter literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column you are updating.
Warning
The Query and View Designer cannot check that a value fits within the length of the column you are updating. If you provide a value that is too long, it might be truncated without warning. For example, if a
namecolumn is 20 characters long but you specify an update value of 25 characters, the last 5 characters might be truncated.Define the rows to update by entering search conditions in the Filter column. For details, see Specifying Search Criteria (Visual Database Tools).
If you do not specify a search condition, all rows in the specified table will be updated.
Note
When you add a column to the Criteria pane for use in a search condition, the Query and View Designer also adds it to the list of columns to be updated. If you want to use a column for a search condition but not update it, clear the check box next to the column name in the rectangle representing the table or table-valued object.
When you execute an Update query, no results are reported in the Results Pane. Instead, a message appears indicating how many rows were changed.
See Also
Concepts
Supported Query Types (Visual Database Tools)
Other Resources
Designing Queries and Views (Visual Database Tools)
Performing Basic Operations with Queries (Visual Database Tools)