Update Question

[Dimitris]

I have two very big tables (about 1.500.000 records each) T1and T2. Those 2
tables have a field with many common data. I'll call it F1. What I want is
to add all the data from a field called F4 of T1 to a new field in T2 (I'll
call it NF).
But I'll add the data of F4 of T1 to NF of T2 only to the records of F1 that
are common in the 2 tables. The field with the common data has the same name
in both tables ( F1 ).
Can someone help me cause I am new in updating queries.
Also because the records are so many, is that a problem at all?
Thank you
Dimitris

 

[John Nurick]

Hi Dimitris

If you can add the new field to the table and compact the database
afterwards, the size of the tables is unlikely to be a problem. (The
most important constraint is the size of the mdb file, which can never
exceed 2GB.)



After that, it's probably a simple update query - provided that
[T1].[F1] is indexed and contains no duplicates.

Create a new query, based on T1 and T2. In the upper part of the query
design window, drag F1 from one table to the other to join the two
tables. Then drag NF from T2 into the "Field:" cell in the first column
of the the query design grid.

Go to the Query menu and select Update Query to turn it into an update
query. In the Update To: cell beneath "NF", put
[T1].[F4]

That's all there is to it. In SQL view, the query will look something
like this:

UPDATE T1 INNER JOIN T2
ON T1.F1 = T2.F1
SET T2.NF = [T1].[F4];

 

[John Vinson]

I have two very big tables (about 1.500.000 records each) T1and T2. Those 2
tables have a field with many common data. I'll call it F1. What I want is
to add all the data from a field called F4 of T1 to a new field in T2 (I'll
call it NF).
But I'll add the data of F4 of T1 to NF of T2 only to the records of F1 that
are common in the 2 tables. The field with the common data has the same name
in both tables ( F1 ).

The first thing to ask - and it's important - is Should I do this at
all? Storing data redundantly (the same data in F1 and NF) is
generally A Very Bad Idea, since there's no protection against the
value being changed in one table or the other.

If that's ok - you are not trying to maintain any integrity between
the tables for this field - then proceed. Otherwise, just use a Query
joining the two tables to retrieve the value of F1 in conjunction with
the data in T2.

This will only work if F1 has a unique Index (such as a Primary Key)
in T2.

If it does, first add the field NF to T2 (if you haven't done so
already). Then create a Query joining T1 to T2 by F1. Change it to an
Update query; on the UpdateTo line under NF put

[T1].[F1]

The brackets are essential - otherwise it will try to update NF to the
text string "T1.F1"!

Run the Query by clicking the ! icon.

Can someone help me cause I am new in updating queries.
Also because the records are so many, is that a problem at all?

You may want to make a cup of coffee while it's running... <g>

No, this should take a few minutes at the most. Compact your database
before and after running, and of course ALWAYS make a backup before
running any big updates!

John W. Vinson[MVP]