update fields with consecutive value

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

Transact-SQL (2012)

update fields with consecutive value

Author

Topic

nirnir2
Starting Member

20 Posts

Posted - 2014-05-26 : 02:35:11

I have a table Table1 with fields
serialNum , integer
Type : integer

Type , serialNum
1 , 1
1 , 2
2 , 3
2 , 4
3 , 10
3 , Null
3 , Null
4 , 20
4 , 21
5 , 22
5 , 40

I need to update all rows where serialNum=null and Type=3 with the consecutive max(serialNum)
The two empty rows should be
3 , 41
3 , 42

whats the most efficient way to do that.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-26 : 03:12:05

[code]
;with aCTE
as
(select 1 Type , 1 serialNum union all
select 1 , 2 union all
select 2 , 3 union all
select 2 , 4 union all
select 3 , 10 union all
select 3 , Null union all
select 3 , Null union all
select 4 , 20 union all
select 4 , 21 union all
select 5 , 22 union all
select 5 , 40)

, bCTE
AS (
select
Type
,serialNum
,row_number() OVER(Order by type,serialNum) as rn
from aCTE A
where A.Type =3
AND A.serialNum IS NULL )

select
[type] ,
b.maxx+ rn as newSerialNum
from bCTE
cross join
(select
max(serialNum) as Maxx
from aCTE B ) B

[/code]

output:
[code]
type SerialNum
3 41
3 42
[/code]

sabinWeb MCP

nirnir2
Starting Member

20 Posts

Posted - 2014-05-26 : 03:22:03

quote:

Originally posted by stepson


;with aCTE
as
	(select 1  Type , 1 serialNum union all
	select 1 , 2 union all
	select 2 , 3 union all
	select 2 , 4 union all
	select 3 , 10 union all
	select 3 , Null union all
	select 3 , Null union all
	select 4 , 20 union all
	select 4 , 21 union all
	select 5 , 22 union all
	select 5 , 40)

, bCTE
AS (
select
	Type
	,serialNum
	,row_number() OVER(Order by type,serialNum) as rn
	from aCTE A		
where A.Type =3 
	AND A.serialNum IS NULL )


select 
	[type] ,	
	b.maxx+ rn as newSerialNum
from bCTE
	cross join 
	(select 
			max(serialNum) as Maxx
		from aCTE B ) B

output:


type	SerialNum
3	41
3	42

sabinWeb MCP

Thanks but all I need is to update the empty SerialNum rows.
I dont need to select anything, it was only example of the new values for those rows .
this is big table with 300,000 rows .

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-26 : 03:40:02

[code]
;with bCTE
AS (
select
Type
,serialNum
,row_number() OVER(Order by type,serialNum) as rn
,B.Maxx
from tblSerialNum A
cross join
(select
max(serialNum) as Maxx
from tblSerialNum B ) B
where A.Type =3
AND A.serialNum IS NULL )

update bCTE
set
serialNum = Maxx +rn
where
Type =3
AND serialNum IS NULL
[/code]

sabinWeb MCP

nirnir2
Starting Member

20 Posts

Posted - 2014-05-26 : 06:58:23

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources