Reassignment Update/Replace Query

[Tiger6115]

I was forced to reassign record numbers in one table to new record numbers and i'm now attempting to update/replace those numbers in another table (Table A). The table being updated (Table A), the record numbers can show up multiple times. I have another table with the Old record number (currently found in Table A) and the reassigned new record number.


Table A
Rcrd Num
12345
12346
12347
12345
12345

Table B
OldNum -NewNum
12345----23456
12346----23457
12347----23458
12348----23459


How can I replace the record numbers in Table A with the new record number if the matching number is found in Table B? Any help/direction is appreciated.

 

[theDBguy]

Make sure you have a backup and try something like:

UPDATE TableA 
INNER JOIN TableB 
ON TableA.RecNum=TableB.OldNum 
SET TableA.RecNum=TableB.NewNum

 

[Tiger6115]

Thank you so much theDBguy!! It worked perfectly.

 

[theDBguy]

Hi. You're welcome. Glad we could assist. Good luck with your project.