How do you create an update query?

This MSAccess tutorial explains how to create an Update query that updates values in one table with values from another table in Access 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2007, I'm trying to update about 6000+ records from one table with values from another table, but I'm having problems. Actually I am trying to update the Bot table MFG with the Big table MFG when Big.PART = Bot.PART.

The query I'm using is:

UPDATE Bot
SET Bot.MFG = (SELECT Big.MFG FROM Big WHERE Big.PART = Bot.PART)
WHERE EXISTS (SELECT Big.MFG FROM Big WHERE Big.PART = Bot.PART);

Could you please help?

Answer: First, you need to create a new query. To do this, select the Create tab in the toolbar at the top of the screen. Then click on the Query Design button in the Other group.

When the Show Table window appears, select the tables that you wish to use in the query and click on the Add button. When you have finished adding your tables, click on the Close button.

In this example, we've selected the Big and Bot tables. You can select multiple tables by holding down the Ctrl key while selecting the table names.

Next, right-click somewhere next to the tables (but not on a table) in the query editor, and select Query Type > Update Query from the popup menu.

Next, build the query like the one below:

This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match.

The SQL for this query is as follows:

UPDATE Big INNER JOIN Bot ON Big.PART = Bot.PART
SET Bot.MFG = [Big].[MFG];

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

UPDATE Table

The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

Example

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Try it Yourself »

The selection from the "Customers" table will now look like this:

UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the ContactName to "Juan" for all records where country is "Mexico":

Example

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

Try it Yourself »

The selection from the "Customers" table will now look like this:

Update Warning!

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

The selection from the "Customers" table will now look like this:

How does update query work in SQL?

What are the steps to create a new query?

What are the 3 update commands in SQL?