Table Updates Headaches

[ddelport]

I have this rather sizy (+- 100 000 records) table for products. I need to update this table frequently by downloading a text file. Now I import the text file with the help of a standard import specification and macro - Easy so far. Now I have two tables : The old one that I use and that needs updating and the new one with either changed records, new records or deleted records. I was thinking of &quot;combining&quot; the two tables with a union querie but how do I tell the querie which is the new information? The Idea is to append new rows that didn't exist but I don't know how to tell the query to look for records (based on a unique Product Code)for items that doesn't exist. Furthermore the rows that was deleted in the new table, I can't really delete in the old table since it may be involved in an existing relationship. Maybe just change the description with a &quot;<&quot; infront or is there a way to &quot;archive&quot; these records in another table for redundant product codes whilst keeping a valid relationship.

 

[MinusM]

Union query is not the solution here. You will have to use a Left Join query.

Make a backup of your existing database before you start.

First Import the New Table in a different name. say NewTable
and Your Table is oldTable
The SQL will look like

INSERT INTO oldTable( ProdNo)
SELECT NewTable.ProdNo
FROM NewTable LEFT JOIN OldTable ON NewTable.ProdNo= OldTable.ProdNo
WHERE (((OldTable.ProdNo) Is Null));

For marking as Archive, Create an Yes/No field Called Archive

UPDATE OldTable LEFT JOIN NewTable ON OldTable.ProdNo = NewTable.ProdNo SET OldTable.Archive = True
WHERE (((NewTable.ProdNo) Is Null));

Best of luck

 

[ddelport]

Thank you MinusM! To be honest I wasn't even aware of the existence of a &quot;left join query&quot; Your suggestion seems to be working just fine. I haven't got that far studying my SQL for Dummies handbook that far yet so I do apologise. It is a bit tough to have a full-time (non-IT) career and try and satisfy my intense need for learning about all these &quot;goodies&quot; at the same time. Luckily for me there is some room for applying my &quot;hobby&quot; at work too, at least then work the becomes play...I do undertake to learn more about SQL so try ME in a couple of weeks/months!