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:
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
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:
1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
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:
1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Juan | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Juan | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
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:
1 | Alfreds Futterkiste | Juan | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Juan | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Juan | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Juan | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Juan | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |