How best to update client table records with a dozen or so changed records from another table?

[MLH]

We have a table (tblClients) with several thousand records.
Because of organizational structure, changes to those records
are maintained in a separate database. Weekly, about a dozen
or so clients change phone numbers, addresses or something.
The table housing changed client data is tblChangedClients. It
is IDENTICAL to tblClients - except for the table name.

We have been taking the recs from tblChangedClients and
manually pasting the changes into tblClients. The common,
unique key field in both tables is [cSSN]. The tables contain
the usual info fields (name, addr, city, state, zip, etc...) as well
as the cSSN field. I have tblChangedClients attached to my
database in which tblClients is local. Can a query be used to
update the dozen rec's in tblClients that need updating. Or, do
I have to do the changes one-by-one using DAO?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[David Schofield]

On Tue, 14 Dec 2004 10:22:21 -0500, MLH <CR**@NorthState.net> wrote:

We have a table (tblClients) with several thousand records.
Because of organizational structure, changes to those records
are maintained in a separate database. Weekly, about a dozen
or so clients change phone numbers, addresses or something.
The table housing changed client data is tblChangedClients. It
is IDENTICAL to tblClients - except for the table name.

We have been taking the recs from tblChangedClients and
manually pasting the changes into tblClients. The common,
unique key field in both tables is [cSSN]. The tables contain
the usual info fields (name, addr, city, state, zip, etc...) as well
as the cSSN field. I have tblChangedClients attached to my
database in which tblClients is local. Can a query be used to
update the dozen rec's in tblClients that need updating. Or, do
I have to do the changes one-by-one using DAO?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hi
You can certainly use queries, in general you need three:

delete clients who no longer appear (if this is required)
DELETE tblClients.*
FROM tblClients LEFT JOIN tblChangedClients ON tblClients.cSSN =
tblChangedClients.cSSN
WHERE tblChangedClients.cSSN Is Null

update fields xxx for clients which are in both tables
UPDATE tblClients INNER JOIN tblChangedClients ON tblClients.cSSN =
tblChangedClients.cSSN SET tblClients.xxx = tblChangedClients.xxx

insert new clients
INSERT INTO tblClients ( cSSN, xxx )
SELECT tblChangedClients.cSSN, tblChangedClients.xxx
FROM tblClients RIGHT JOIN tblChangedClients ON tblClients.cSSN =
tblChangedClients.cSSN
WHERE tblClients.cSSN Is Null

David

[MLH]

Thanks, David...
All I need is to update clients that exist in both tables, so I'll
try your 2nd recommendation and let you know how it turns
out. I appreciate the reply!

<snip>

Hi
You can certainly use queries, in general you need three:

delete clients who no longer appear (if this is required)
DELETE tblClients.*
FROM tblClients LEFT JOIN tblChangedClients ON tblClients.cSSN =
tblChangedClients.cSSN
WHERE tblChangedClients.cSSN Is Null

update fields xxx for clients which are in both tables
UPDATE tblClients INNER JOIN tblChangedClients ON tblClients.cSSN =
tblChangedClients.cSSN SET tblClients.xxx = tblChangedClients.xxx

insert new clients
INSERT INTO tblClients ( cSSN, xxx )
SELECT tblChangedClients.cSSN, tblChangedClients.xxx
FROM tblClients RIGHT JOIN tblChangedClients ON tblClients.cSSN =
tblChangedClients.cSSN
WHERE tblClients.cSSN Is Null

David