| re: How best to update client table records with a dozen or so changed records from another table?
On Tue, 14 Dec 2004 10:22:21 -0500, MLH <CRCI@NorthState.net> wrote:
[color=blue]
>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
>[/color]
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
| 
