I've run into a case in which I'm unsure how to write the SQL to accomplish what I need. Here's a quick breakdown of the tables in question:
So, in table A, I have a number that goes with each record, but that number may not be unique. In table B, I have that field as well and it is working as a foreign key but, given that it's not guaranteed to be unique, this needs to be changed.
So, what I want to do is this:
For every record in B, change RecordNumber to be the value of A.UniqueRecordID where A.RecordNumber = B.RecordNumber.
Unfortunately, I just don't know how to write the SQL for that. Any suggestions?
From a database point of view, this would have to be done in more than one step. It can't be a single SQL update query because recordNumber would not still be the FK.
You could do: 1) Drop FK constraint on recordNumber 2) Update all values 3) Add new PK/FK constaint against UniqueRecordId
I would prefer the following though: 1) Add a new column called UniqueRecordId to table B with a FK constraint 2) Populate all values in new column 3) Optionally make UniqueRecordId a PK 3) Drop recordNumber
What do you want the value in table B to be replaced with?
Hmm...I guess I should point out that, as of right now, all RecordNumber values are, in fact, unique. We're looking to expand the application and, in doing some, those numbers may not be unique, in the future. However, I need to find a way to preserve the data that already exists in the database.
Perhaps that helps clear things up a bit. Basically, the example you laid out can't happen right now. Every record in A will match up with exactly 1 record in B (and vice versa), matching on RecordNumber.