Query Problem

Hi to All!
we have recently updated our reference codes from 3 digits to 7 digits
due to shortage of reference codes
first we have reference no like
reference no
101
102
103
.
.
.
now we have changed the reference nos to 7 digits due to some
constraints like
1001101
1001201
3001101
1001102
2001102
.
.
.
i have table in which both old and aginst them new no is stored like
Old_Code New_Code
101 1001101
201 1001201
103 1001103
in production table reference no comes like
reference no
101-ABC-06
201-DEF-06
.
.
.
in production table i have more than 1 million records. now
want to update old reference no with new one like
Old reference no New_reference_no
101-ABC-06 1001103-ABC-06
201-DEF-06 1001101-DEF-06
.
.
.
how would i b able to update new reference codes in production table
What will be the query to update the old record
Thanx
Farid
*** Sent via Developersdex http://www.examnotes.net ***Can you give the create table scripts.
--
"Jami" wrote:

> Hi to All!
> we have recently updated our reference codes from 3 digits to 7 digits
> due to shortage of reference codes
> first we have reference no like
> reference no
> 101
> 102
> 103
> ..
> ..
> ..
> now we have changed the reference nos to 7 digits due to some
> constraints like
> 1001101
> 1001201
> 3001101
> 1001102
> 2001102
> ..
> ..
> ..
> i have table in which both old and aginst them new no is stored like
> Old_Code New_Code
> 101 1001101
> 201 1001201
> 103 1001103
> in production table reference no comes like
> reference no
> 101-ABC-06
> 201-DEF-06
> ..
> ..
> ..
> in production table i have more than 1 million records. now
> want to update old reference no with new one like
> Old reference no New_reference_no
> 101-ABC-06 1001103-ABC-06
> 201-DEF-06 1001101-DEF-06
> ..
> ..
> ..
> how would i b able to update new reference codes in production table
> What will be the query to update the old record
> Thanx
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||
Create Table Reference_Codes (Old_code char(3), New_Code char(7))
go
Create table main_table(Main_Id Char(5), Reference_code char(14),name
varchar(55)... )
here first referece code is of 10 characters with following format
101-ABC-06
now it is
1001101-ABC-06
*** Sent via Developersdex http://www.examnotes.net ***|||Try this.. Hope this helps.
update A
set Reference_code = left(new_code,4) + Reference_code
from main_table A, Reference_Codes B
where left(A.reference_code,3) = B.Old_Codesql