How to update this data - 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

General SQL Server Forums

New to SQL Server Programming

How to update this data

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-08-21 : 13:14:22

How do i Snum like following output

Declare @tbl1 table
(ID INT,
AID INT,
Alvl INT,
SNum INT
)

INSERT INTO @tbl1
SELECT 1, 101,0,NULL

INSERT INTO @tbl1
SELECT 2, 101,1,NULL

INSERT INTO @tbl1
SELECT 3, 101,1,NULL

INSERT INTO @tbl1
SELECT 4, 101,1,NULL

INSERT INTO @tbl1
SELECT 5, 101,1,NULL

INSERT INTO @tbl1
SELECT 6, 101,1,NULL

INSERT INTO @tbl1
SELECT 7, 102,0,NULL

INSERT INTO @tbl1
SELECT 8, 102,1,NULL

INSERT INTO @tbl1
SELECT 9, 102,1,NULL

INSERT INTO @tbl1
SELECT 10, 102,1,NULL

INSERT INTO @tbl1
SELECT 11, 0,0,NULL

INSERT INTO @tbl1
SELECT 12, 103,0,NULL

INSERT INTO @tbl1
SELECT 13, 103,1,NULL

Select * from @tbl1

The Output looks like :

ID AID Alvl SNum
1 101 0 0
2 101 1 1
3 101 1 2
4 101 1 3
5 101 1 4
6 101 1 5
7 102 0 0
8 102 1 1
9 102 1 2
10 102 1 3
11 0 0 0
12 103 0 0
13 103 1 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-21 : 13:32:10

[code]Declare @tbl1 table
(ID INT,
AID INT,
Alvl INT,
SNum INT
)

INSERT INTO @tbl1
SELECT 1, 101,0,NULL

INSERT INTO @tbl1
SELECT 2, 101,1,NULL

INSERT INTO @tbl1
SELECT 3, 101,1,NULL

INSERT INTO @tbl1
SELECT 4, 101,1,NULL

INSERT INTO @tbl1
SELECT 5, 101,1,NULL

INSERT INTO @tbl1
SELECT 6, 101,1,NULL

INSERT INTO @tbl1
SELECT 7, 102,0,NULL

INSERT INTO @tbl1
SELECT 8, 102,1,NULL

INSERT INTO @tbl1
SELECT 9, 102,1,NULL

INSERT INTO @tbl1
SELECT 10, 102,1,NULL

INSERT INTO @tbl1
SELECT 11, 0,0,NULL

INSERT INTO @tbl1
SELECT 12, 103,0,NULL

INSERT INTO @tbl1
SELECT 13, 103,1,NULL

--Select * from @tbl1

select
id,
aid,
alvl,
row_number() over (partition by aid order by alvl)-1 as snum
from @tbl1[/code]

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

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-08-21 : 14:06:11

Thanks, webfred
How do i update on snum field?
I tried this way but getting err:

update t
set snum= row_number() over (partition by aid)-1
from @tbl1 t

select * from @tbl1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-21 : 14:25:46

[code]update a
set a.snum = b.snum
from @tbl1 a
inner join
(
select
id,
aid,
alvl,
row_number() over (partition by aid order by alvl)-1 as snum
from @tbl1
) b
on a.id = b.id[/code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-08-21 : 15:21:41

Thabnks, vijayisonly

Still problems.
If there is aid=0 than need to update snum also 0

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-21 : 15:58:52

Use a case statement

update a
set a.snum = case a.aid when 0 then 0 else b.snum end
from @tbl1 a
inner join
(
select
id,
aid,
alvl,
row_number() over (partition by aid order by alvl)-1 as snum
from @tbl1
) b 
on a.id = b.id