How to update column with 1st row of same group? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

How to update column with 1st row of same group?

Author

Topic

Adya1979
Starting Member

2 Posts

Posted - 2010-07-23 : 04:32:10

Hi,

I want to update column with the 1st row of same group. Below are the sample of data,

Id Sur_No Sur_Type
1 5 null
1 3 null
1 10 null
2 3 null
2 11 null

This is the expected output,

Id Sur_No Sur_Type
1 5 5
1 3 null
1 10 null
2 3 3
2 11 null

This is what i've done,


UPDATE [myTable] SET [Sur_Type]=(SELECT TOP 1 Sur_No FROM [myTable])

and this is the output,

Id Sur_No Sur_Type
1 5 5
1 3 5
1 10 5
2 3 5
2 11 5

any idea how to resolve this problem? thanks in advance :)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-23 : 07:11:44

Try This:

UPDATE [myTable] SET [Sur_Type]=Case When Subtab.Srno =1 then T.Sur_no else null end
From [myTable] T join (Select Row_number() over (Partition by Id order by Sur_no) as Srno ,Id, sur_no
from [myTable] ) As SubTab
On T.Id = SubTab.ID
and T.Sur_no = Subtab.Sur_no

Example:

declare @Test table
(
Id int,
Sur_no int,
Sur_type int)

Insert into @Test
Select 1,5, null union
Select 1,3 ,null union
Select 1,10 ,null union
Select 2,3, null union
Select 2,11, null

Select * from @Test

Update @Test set Sur_Type = Case When Subtab.Srno =1 then T.Sur_no else null end
From @Test T join (Select Row_number() over (Partition by Id order by Sur_no) as Srno ,Id, sur_no
from @Test ) As SubTab
On T.Id = SubTab.ID
and T.Sur_no = Subtab.Sur_no

Select * from @Test

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-23 : 07:17:42

quote:
Originally posted by pk_bohra

Try This:

UPDATE [myTable] SET [Sur_Type]=Case When Subtab.Srno =1 then T.Sur_no else null end
From [myTable] T join (Select Row_number() over (Partition by Id order by Sur_no) as Srno ,Id, sur_no
from [myTable] ) As SubTab
On T.Id = SubTab.ID
and T.Sur_no = Subtab.Sur_no

Example:

declare @Test table
(
Id int,
Sur_no int,
Sur_type int)

Insert into @Test
Select 1,5, null union
Select 1,3 ,null union
Select 1,10 ,null union
Select 2,3, null union
Select 2,11, null

Select * from @Test

Update @Test set Sur_Type = Case When Subtab.Srno =1 then T.Sur_no else null end
From @Test T join (Select Row_number() over (Partition by Id order by Sur_no) as Srno ,Id, sur_no
from @Test ) As SubTab
On T.Id = SubTab.ID
and T.Sur_no = Subtab.Sur_no

Select * from @Test

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Row_number() can't be available in Mssql 2000.

Note: Posted in 2000 Forum

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-23 : 07:34:25

try this:


create table Ad
(id int
,Snu int
,Stype int
)

insert into Ad
select 1, 5, null union all
select 1, 3, null union all
select 1, 10, null union all
select 2, 3, null union all
select 2, 11, null union all
select 3, 4, null union all
select 3, 1, null
--(7 row(s) affected)

select * from ad

update ad
set ad.stype =	(select x.snu from (
					(select
					ad.id 
					,ad.snu
					,row_number() over (partition by id order by id) as R_N
					from ad)) as x 
					where x.R_N = 1
					and x.id = ad.id)
from ad 

select * from ad


drop table ad

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-23 : 09:32:09

quote:
Originally posted by senthil_nagore

Row_number() can't be available in Mssql 2000.

Note: Posted in 2000 Forum

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

I agree with you. I didn't noticed the version.
Thanks

I am here to learn from Masters and help new bees in learning.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-24 : 02:10:20

this is SQL 2000 solution. You will see I added extra identity column which is in this case needed.


create table Ad
(id_identity int identity(1,1)
,id int
,Snu int
,Stype int
)

insert into Ad
select 1, 5, null union all
select 1, 3, null union all
select 1, 10, null union all
select 2, 3, null union all
select 2, 11, null union all
select 3, 4, null union all
select 3, 1, null
--(7 row(s) affected)


update ad
set ad.stype = (select x.snu from
						(
						select 
						a1.id
						,a1.snu
						,a1.stype
						,(select count(0)+1
							from ad as a2
								where a1.id = a2.id
									and a1.id_identity > a2.id_identity
						)as R_N
							from ad as a1
						) x
							where x.R_N = 1
							and x.id = ad.id
				)
from ad

select * from ad
drop table ad

Adya1979
Starting Member

2 Posts

Posted - 2010-07-25 : 21:53:21

Hi all,

Thanks for the reply..
I'm using version 2000 and tested the code from slimt_slimt and it works

But i will also try pk_bohra's coding in different version of sql.

Thanks to all for your help..really appreciate it..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources