Remove field from composite key and collate duplicate data I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again.  The modification I'm going to make is going to remove part of the primary key, which will result in tens of thousands of rows which need to be collated.

The part of the key being removed is a date field, which originally was needed to help uniquely identify incoming data.  However, the way this field is assigned at the source (outwith my control) has since changed and we now want to only keep the earliest occurrence of a particular record and discard the later occurrences.

The rest of the fields in the key (4 FK's) will produce unique records.

I'm assuming I'll need to transfer the data to another table, make the change and then re-insert?  Or is there a better way?
If the four records dictate a unique and you want the first one by date, then why not insert into another table, truncate, then insert back once you finish the table reconfigure (or create a new table with the right columns, insert into, then rename after dropping?) I don't think this can be improved upon, unless you want to delete from where not equal to, but the insert into should be faster (needs to be tested to prove for sure)