need an update query - SQL Server Forums

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)

need an update query

Author

Topic

jenskp
Starting Member

5 Posts

Posted - 2010-10-23 : 06:15:11

Hi all,

I need an update query which updates the pid column of my given below table.

S_No Userid uid pid

1 4 0 null
2 7 1 null
3 8 1 null
4 9 2 null

After updation, i need the values of pid column as

S_No Userid uid pid

1 4 0 0
2 7 1 4
3 8 1 4
4 9 2 7

When uid value is 0,i need the pid value as 0 itself.

But,when uid value is 1,pid column should be updated with the corresponding Userid value(ie; 4) for the S_No 1

when uid value is 2,pid column should be updated with the corresponding Userid value(ie; 7) for the S_No 2

Please help me..very urgent

- Thanks
Jenskp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-23 : 06:25:18

Will the values of uid always be 0 or 1 or 2?
Or could you say that, except for uid=0, the pid is always to set with the userid that corresponds via uid to the s_no?

No, you're never too old to Yak'n'Roll if you're too young to die.

jenskp
Starting Member

5 Posts

Posted - 2010-10-23 : 06:47:39

Yes.

The values of uid will be always 0,1,1,2,2,3,3,4,4,...

Except for uid=0, the pid is always to set with the userid that corresponds via uid to the s_no.

- Thanks
Jenskp

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-23 : 07:07:28

Try this:

Declare @MyTable table
(S_no int,
Userid int,
Uid int,
Pid int)

Insert into @MyTable
Select 1,4,0,null union
Select 2,7,1,null union
Select 3,8,1,null union
Select 4,9,2,null

Update @MyTable Set Pid= isnull(SubTab.Userid,0)
From @MyTable MainTab
inner join
(
Select M.S_no, Sub.UserId
from @MyTable M left join @MyTable Sub
On M.Uid = Sub.S_no
) as SubTab on MainTab.S_no = SubTab.S_no

Select * from @MyTable

jenskp
Starting Member

5 Posts

Posted - 2010-10-23 : 07:37:03

Hi,

I tried the procedure..But,its giving all pid column values as 0.

- Thanks
Jenskp

jenskp
Starting Member

5 Posts

Posted - 2010-10-23 : 07:50:16

Hi pk_bohra,

Thank you so much.

procedure is working now and am getting the correct pid values.

There was a spelling mistake in one of the column names when i typed the procedure.That was the reason why i was getting 0's as pid values.

sorry for that and thank you.

- Thanks
Jenskp

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-23 : 14:06:56

quote:
Originally posted by jenskp

Hi pk_bohra,

Thank you so much.

procedure is working now and am getting the correct pid values.

There was a spelling mistake in one of the column names when i typed the procedure.That was the reason why i was getting 0's as pid values.

sorry for that and thank you.

- Thanks
Jenskp

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources