Tricky Update Statement

[Corey McGlone]

Hi all,

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:

+--------------------+
|          A         |
+--------------------+
|    RecordNumber    |
|   UniqueRecordID   |
+--------------------+
 
+--------------------+
|          B         |
+--------------------+
|    RecordNumber    |
+--------------------+

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?

Thanks,
Corey

[Jeanne Boyarsky]

Corey,
I don't understand what you are trying to do. (I do get what you are trying to accomplish - a unique key for table B. It's the approach I don't follow.)

Suppose you have the following:
Table A:
Record Number - 1, Unique Record ID - 1
Record Number - 1, Unique Record ID - 2

Table B:
Record Number - 1

What do you want the value in table B to be replaced with?

[Jeanne Boyarsky]

Ignoring my above question...

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

[Corey McGlone]

Originally posted by Jeanne Boyarsky:

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.

[Jeanne Boyarsky]

Corey,
That makes sense. The technique is still the same though. (from my October 25, 2005 05:47 PM post)