Deleting *Somewhat* Duplicate Records

[Netherworld]

I have inherited a database where system changes are tracked. We receive drafts of these changes. Many times, these changes have multiple drafts. Originally, the users would enter a new record when a subsequent draft would come in.

Once a new draft is received, we no longer need to continue working with the prior draft but don't want to delete it completely. We have set up a table to house historical data on drafts and only want to keep the current draft in the main table.

The primary key is a combination of Change and Draft:
Change Draft
AAAA 1
AAAA 2
BBBB 1
CCCC 1
DDDD 1
DDDD 2
DDDD 3
EEEE 1

I need two separate things to happen here.
1) I want to rid the main table of all the old drafts while keeping the latest drafts.
2) I want to append the old drafts to the historical table excluding the latest drafts.

I am new to SQL, so use the "Dummies" language please!

Thank you!!!

 

[randy700]

I believe the best approach would be to add another field to the current table (call it Status).
Only the most recent draft would be Active, all others Inactive.

If you still want to utilize two tables, you'll need to run two queries.
First, an append query to copy from your current table to your archives.
Second, a delete query to remove the data from the current table.

Hope this helps.


Randy

 

[Netherworld]

As much sense as that makes, my boss wants the old records in a separate table.

 

[Golom]

To copy everything but the latest Draft to the history table

INSERT INTO Historical (Change, Draft, ... Other Fields ...)

Select Change, Draft, ... Other Fields ... From Main M

Where M.Draft < (Select MAX(Draft) From Main X
                 Where X.Change = M.Change)

and to remove them from the Main table

DELETE * From Main M

Where M.Draft < (Select MAX(Draft) From Main X
                 Where X.Change = M.Change)