How to do this Update Query

  • Thread starter Thread starter Tod
  • Start date Start date
T

Tod

This is my first attept at an update query and I'm already
lost. I have a table with about 400,000 records. I have
another table with about 2,000 records. The primary key
and foreign key is called Part_N. I want to find each
Part_N in the Main table that matches a Part_N in the
second table and change the Location and Bin fields for
those records in the main table to reflect the Location
and Bin fields in the second table. Does that all make
sense?

How do I do this?

tod
 
You should not need to store the Location and Bin fields in both tables. As
long as the tables are linked on the Primary Key/Foreign Key link, then you
should be able to pull out the correct data any time you need with a simple
select query.
 
Put both tables in a query
Join on the Part_n fields
Change the query to an update query
Put the Main table fields in fields and table
type the secondtable's name and field name with brackets in the update to row
under the relevant fields.

SQL statement would look something like:

UPDATE MainTable INNER JOIN SecondTable
On MainTable.Part_N = SecondTable.Part_N
Set Maintable.Location =[SecondTable].[Location],
Maintable.Bin = [SecondTable].[Bin]

Add a where clause if you don't want to update if values are null
WHERE Secondtable.location is not null and SecondTable.bin is not null

Try this ON A COPY of your table to see if you get the desired results.
 
Thanx John. I figured it out after my original post. What made me so
nervous is that when I clicked on the dataset button to see what the
results would look like, it only showed me the changed columns. I was
afraid it was going to replace my main table with just those two
columns so I made a backup of the table just in case. Then I ran the
update query. Now that I see how it works I wont be so nervous next
time.

Thanx again,
tod

John Spencer (MVP) said:
Put both tables in a query
Join on the Part_n fields
Change the query to an update query
Put the Main table fields in fields and table
type the secondtable's name and field name with brackets in the update to row
under the relevant fields.

SQL statement would look something like:

UPDATE MainTable INNER JOIN SecondTable
On MainTable.Part_N = SecondTable.Part_N
Set Maintable.Location =[SecondTable].[Location],
Maintable.Bin = [SecondTable].[Bin]

Add a where clause if you don't want to update if values are null
WHERE Secondtable.location is not null and SecondTable.bin is not null

Try this ON A COPY of your table to see if you get the desired results.
This is my first attept at an update query and I'm already
lost. I have a table with about 400,000 records. I have
another table with about 2,000 records. The primary key
and foreign key is called Part_N. I want to find each
Part_N in the Main table that matches a Part_N in the
second table and change the Location and Bin fields for
those records in the main table to reflect the Location
and Bin fields in the second table. Does that all make
sense?

How do I do this?

tod
Click to expand...
Click to expand...
 
Thanx John. I figured it out after my original post. What made me so
nervous is that when I clicked on the dataset button to see what the
results would look like, it only showed me the changed columns. I was
afraid it was going to replace my main table with just those two
columns so I made a backup of the table just in case. Then I ran the
update query. Now that I see how it works I wont be so nervous next
time.

Thanx again,
tod

John Spencer (MVP) said:
Put both tables in a query
Join on the Part_n fields
Change the query to an update query
Put the Main table fields in fields and table
type the secondtable's name and field name with brackets in the update to row
under the relevant fields.

SQL statement would look something like:

UPDATE MainTable INNER JOIN SecondTable
On MainTable.Part_N = SecondTable.Part_N
Set Maintable.Location =[SecondTable].[Location],
Maintable.Bin = [SecondTable].[Bin]

Add a where clause if you don't want to update if values are null
WHERE Secondtable.location is not null and SecondTable.bin is not null

Try this ON A COPY of your table to see if you get the desired results.
This is my first attept at an update query and I'm already
lost. I have a table with about 400,000 records. I have
another table with about 2,000 records. The primary key
and foreign key is called Part_N. I want to find each
Part_N in the Main table that matches a Part_N in the
second table and change the Location and Bin fields for
those records in the main table to reflect the Location
and Bin fields in the second table. Does that all make
sense?

How do I do this?

tod
Click to expand...
Click to expand...
 
Back
Top