By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.