Replace row value using update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replace row value using update

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-10 : 10:07:25

My AutoNo table as follow,
tAutoNo
Pref | cYear | cMonth | No
-----------------------------------
_R | 2010 | 01 | 1
*No is int

tRoute
TrnxID | RID
-------------------
1 | _r00010
2 | _r00006
3 | _r00024
4 | _r00030
5 | _r00044
6 | _r00047
*TrnxID is int and a primary key
*RID is varchar(7) and unique

1. I want RID value in table tRoute will be replaced with New RID value
2. The new RID value taken from tAutoNo
3. The new RID value is a 8 character, combination of Pref+00000+No
4. Every No is taken from tAutoNo, No is increase by 1
5. Example of new RID value is _R000001, _R000002, _R000003, ...., _R000009, _R000010, _R000011, and so on

So, the result as follow,
Before
tRoute
TrnxID | RID
-------------------
1 | _r00010
2 | _r00006
3 | _r00024
4 | _r00030
5 | _r00044
6 | _r00047

After
tRoute
TrnxID | RID
-------------------
1 | _R000001
2 | _R000002
3 | _R000003
4 | _R000004
5 | _R000005
6 | _R000006

tAutoNo
Pref | cYear | cMonth | No
-----------------------------------
_R | 2010 | 01 | 6

I believe T-SQL have while and update.

Anyone can show me how T-SQL looks like?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-10 : 10:36:53

[code]Update TR
Set TR.RID = TA.Pref + '00000' + Cast(No as Varchar(4))
from TRoute TR
inner join tAutoNo TA
On TA.No = TR.TrnxID[/code]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-10 : 11:47:00

quote:

Originally posted by sodeep

Update TR
Set TR.RID = TA.Pref + '00000' + Cast(No as Varchar(4))
from TRoute TR
inner join tAutoNo TA 
On TA.No = TR.TrnxID

The result, there's no looping. Also No in tAutoNo not increase by 1 after each updated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-10 : 11:55:20

You don't need to loop for it. It can handle it in one statement.

UPDATE tRoute
SET RID = '_R' + RIGHT('000000' + CONVERT(varchar(5), TrnxID), 6)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-10 : 12:06:22

quote:
Originally posted by tkizer

You don't need to loop for it. It can handle it in one statement.

UPDATE tRoute
SET RID = '_R' + RIGHT('000000' + CONVERT(varchar(5), TrnxID), 6)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

That's great. How about if i want to start from No=45. so, the 1st number will be a _R000045, and so on

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-10 : 12:47:39

Then add 44 to TrnxID in the above query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-10 : 18:17:19

quote:
Originally posted by tkizer

Then add 44 to TrnxID in the above query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Second row value is not increase by 1. So, the result is duplicate.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-10 : 19:24:38

[code]
Declare @T table(TrnxID int, RID Varchar(40))

Insert @T

Select 1 ,'_r00010' union all
Select 2 ,'_r00006' union all
Select 3 ,'_r00024' union all
Select 4 ,'_r00030' union all
Select 5 ,'_r00044' union all
Select 6 ,'_r00047'

Update T
Set T.RID = '_R' + '00000' + Cast(T.TrnXID+44 as Varchar(10))
from @T T
inner join @T M on M.TrnXID = T.TrnXID

Select * from @T[/code]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-10 : 21:17:51

mr tkizer and mr sodeep, both of you are great.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-11 : 13:29:05

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources