sql server programming: Query Problem -- sql server programming

Query Problem

Jami
5/2/2006 11:58:32 PM

sql server programming: 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

RE: Query Problem

Omnibuzz
5/3/2006 12:23:02 AM

Can you give the create table scripts.
--

[quoted text, click to view]

RE: Query Problem

Jami
5/3/2006 1:08:44 AM

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

RE: Query Problem

Omnibuzz
5/3/2006 1:47:03 AM

Try this.. Hope this helps.

update A
set Reference_code = left(new_code,4) + Reference_code
from main_table A, Reference_Codes B

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.