You should be able to copy the PK field over to the New table using a similar query...
-- Create the PK column in the NewTable, then run the update -- Update NewTable Set NewTable.PK = OldTable.PK Where old Table.LastName = NewTable.LastName AND oldTable.FirstName = NewTable.FirstName (you can continue matching fields) Then you can delete the ones that don't match Delete from OldTable Where oldTable.PK Not IN (Select PK from NewTable) This isnt tested.... I used to have to do these data import/transforms/whateva all the time at my previous job. I kinda miss it...lol Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 10/16/2002 12:05:15 PM >>> They have identical fields... The new db does not have a pk, but rather a memNumber field which is also in the old db... The old db does have a pk field. The issue is I need to delete records out of the old db if they are not in the new db...(which means a member has cancelled) and insert any new members from the new one into the old one... <sigh> ~~ Stephenie > |-----Original Message----- > |From: Harkins,Patrick [mailto:[EMAIL PROTECTED]] > |Sent: Wednesday, October 16, 2002 12:02 PM > |To: CF-Community > |Subject: RE: Db merge question > | > | > |I would create a new db and link the two tables that you > |want to merge but that part is optional. > | > |Then I would write a union query on the two tables which > |will automatically not include duplicates - see UNION query > |documentation in Access. This only works if the two tables > |have identical fields.... There are plenty of side > |considerations, but this might work for you. > | > |Patrick > | > | > |> -----Original Message----- > |> From: Stephenie Hamilton [mailto:[EMAIL PROTECTED]] > |> Sent: Wednesday, October 16, 2002 11:42 AM > |> To: CF-Community > |> Subject: Db merge question > |> > |> > |> I am not at my best today and am writing an app to merge 2 access > |> db's.... I have the insert of new records covered, but am > |fuzzy on the > |> best way to check to see if a record in the old db is not > |in the new > |> db, which means that record needs to be deleted from the old db... > |> > |> What is the best way to do this? > |> TIA > |> > |> ~~ > |> Stephenie > |> > |> > |> > |> > |> --- > |> Outgoing mail is certified Virus Free. > |> Checked by AVG anti-virus system (http://www.grisoft.com). > |> Version: 6.0.401 / Virus Database: 226 - Release Date: 10/9/2002 > |> > |> > |> > | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=5 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_community Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
