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 was 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?