Help with update Query

[GastonFranzini]

hi,
I have two tables, same heading. I need to update the records in one
depending the values on the other.I have an unique ID.
I can´t work it out.
Thanks

 

[John Vinson]

hi,
I have two tables, same heading. I need to update the records in one
depending the values on the other.I have an unique ID.
I can´t work it out.
Thanks

WHY!?

You're storing the same data redundantly in two identically structured
tables? That seems like a major violation of database principles.

If you do need to do it for some reason I don't understand, you would
create a query joining the two tables by ID; change the query to an
Update query; and on the Update To line under [Table1].[Field1] put
[Table2].[Field1]. The brackets are required (otherwise it will try
to update the field to the text string "Table2.Field1"). Of course you
need to use your own table and fieldnames, which you have chosen not
to share with us.

John W. Vinson[MVP]

 

[Guest]

Assuming that the unique ID has the same value in both tables, join the
tables by the unique ID and update Table2's fields with the values in the
corresponding Table1 record.

For tables called Customer1 & Customer2 with a unique CustID, the following
example sets the address fields:

UPDATE Customer1
INNER JOIN Customer2 ON Customer1.CustID = Customer2.CustID
SET Customer2.CompanyName = [Customer1].[CompanyName], Customer2.Address =
[Customer1].[Address],
Customer2.City = [Customer1].[City],
Customer2.State = [Customer1].[State],
Customer2.Zip = [Customer1].[Zip];

Hope that helps.

Sprinks

 

[GastonFranzini]

John,
Thanks for your comments.
I need to import an excel woorksheet and then I want to update the
records of the table.
I have more than 20 fields, in each table. Do I need to write each
field in the update to ?
Thanks again


John Vinson ha escrito:

hi,
I have two tables, same heading. I need to update the records in one
depending the values on the other.I have an unique ID.
I can´t work it out.
Thanks

WHY!?

You're storing the same data redundantly in two identically structured
tables? That seems like a major violation of database principles.

If you do need to do it for some reason I don't understand, you would
create a query joining the two tables by ID; change the query to an
Update query; and on the Update To line under [Table1].[Field1] put
[Table2].[Field1]. The brackets are required (otherwise it will try
to update the field to the text string "Table2.Field1"). Of course you
need to use your own table and fieldnames, which you have chosen not
to share with us.

John W. Vinson[MVP]

 

[John Vinson]

John,
Thanks for your comments.
I need to import an excel woorksheet and then I want to update the
records of the table.
I have more than 20 fields, in each table. Do I need to write each
field in the update to ?

My dentist has a sign:

Do I need to floss all my teeth?
Only the ones you want to keep.

In your case... do I need to write each field in the update to? Only
the ones you want to update.

John W. Vinson[MVP]