Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Tricky Update Statement

 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Corey,
That makes sense. The technique is still the same though. (from my October 25, 2005 05:47 PM post)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic