range update - 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

range update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-04 : 11:49:50

Hello,
How do i update col2 with range of col1 value?
For more detail look at below the output

Declare @tbl1 table
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A001',NULL

INSERT INTO @tbl1
SELECT 2,'A002',NULL

INSERT INTO @tbl1
SELECT 3,'A003',NULL

INSERT INTO @tbl1
SELECT 4,'A004',NULL

INSERT INTO @tbl1
SELECT 5,'A005',NULL

INSERT INTO @tbl1
SELECT 6,NULL,NULL

INSERT INTO @tbl1
SELECT 7,'A006',NULL

INSERT INTO @tbl1
SELECT 8,Null,NULL

INSERT INTO @tbl1
SELECT 9,'A007',NULL

INSERT INTO @tbl1
SELECT 10,'A008',NULL

INSERT INTO @tbl1
SELECT 11,'A009',NULL

Output look like this:

ID Col1 Col2
1 A001 A001-A003
2 A002 A001-A003
3 A003 A001-A003
4 A004 A004-A006
5 A005 A004-A006
6 NULL NULL
7 A006 A004-A006
8 NULL NULL
9 A007 A007-A009
10 A008 A007-A009
11 A009 A007-A009

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 12:15:13

I can see no rule to that output.

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 12:25:18

Rudba..how come you always have some strange updates that need to be done?

and like webfred says..what is the logic behind this update you are looking for?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 13:57:44

Here is a quick and dirty solution:

UPDATE T
	SET Col2 = D.Col2
FROM
	@tbl1 AS T
LEFT OUTER JOIN
	(
		SELECT	
			A.ID,
			A.Col1,
			CASE 
				WHEN B.RowNum % 3 = 1 THEN B.Col1 + '-' + A.Col1
				ELSE NULL
			END AS Col2
		FROM
			(
				SELECT 
					ID, Col1,
					ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
				FROM
					@tbl1
				WHERE
					Col1 IS NOT NULL
					OR Col2 IS NOT NULL
			) AS A
		LEFT OUTER JOIN
			(
				SELECT 
					ID, Col1,
					ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
				FROM
					@tbl1
				WHERE
					Col1 IS NOT NULL
					OR Col2 IS NOT NULL
			) AS B
			ON A.RowNum = B.RowNum + 2
	) AS D
	ON T.ID = D.ID

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-04 : 15:11:29

its looks good, but if i want to like this :

ID Col1 Col2
1 A001 A001-A003
2 A002 A001-A003
3 A003 A001-A003
4 A004 A004-A006
5 A005 A004-A006
6 NULL NULL
7 A006 A004-A006
8 NULL NULL
9 A007 A007-A009
10 A008 A007-A009
11 A009 A007-A009

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 15:35:32

What did have you tried so far?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-08 : 12:30:53

Lamprey,
Where do i have to change your script to get following output:

1 A001 A001-A003
2 A002 A001-A003
3 A003 A001-A003
4 A004 A004-A006
5 A005 A004-A006
6 NULL NULL
7 A006 A004-A006
8 NULL NULL
9 A007 A007-A009
10 A008 A007-A009
11 A009 A007-A009

quote:
Originally posted by Lamprey

What did have you tried so far?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-09 : 14:51:54

Guys anybody have solution?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-09 : 15:44:26

There is probably a better way, but this seems to work:

;WITH Table1
AS
(
	SELECT 
		ID, Col1,
		ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
	FROM
		@tbl1
	WHERE
		Col1 IS NOT NULL
		OR Col2 IS NOT NULL
)

UPDATE T
	SET Col2 = D.Col2
FROM
	@tbl1 AS T
LEFT OUTER JOIN
	(
		SELECT	
			A.ID,
			A.Col1,
			B.Col1 + '-' + C.Col1 AS Col2
		FROM
			Table1 AS A
		INNER JOIN
			Table1 AS B
			ON 
			(
				B.RowNum = A.RowNum  
				OR B.RowNum + 1 = A.RowNum 
				OR B.RowNum  + 2 = A.RowNum
			)
			AND B.RowNum % 3 = 1 
		INNER JOIN
			Table1 AS C
			ON
			(
				C.RowNum = A.RowNum  
				OR C.RowNum - 1 = A.RowNum 
				OR C.RowNum - 2 = A.RowNum
			)
			AND C.RowNum % 3 = 0 
	) AS D
	ON T.ID = D.ID

Changed to CTE.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-11 : 11:31:13

Lamprey,

It works for RowNum % 3 but if i have to change like 100 it does not works.
Thanks

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-11 : 12:35:15

Try on this data.
It tooks long time just to complete 1200 data. And also null value could be anywhere.

Declare @tbl1 table
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)
DECLARE @Counter Int
DECLARE @Col1 varchar(50)
SET @Counter = 1
WHILE @Counter < =1200
BEGIN
SET @Col1= 'A'+ CONVERT(VarChar(10), @Counter)
INSERT INTO @tbl1 (id,Col1,Col2) VALUES (@Counter,@Col1,NULL)

SET @Counter = @Counter + 1
END

Update @tbl1 Set Col1=Null Where ID in (5,22,10,33,199,223,300,403,602,700,701,7002)
;WITH Table1
AS
(
SELECT
ID, Col1,
ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM
@tbl1
WHERE
Col1 IS NOT NULL
OR Col2 IS NOT NULL
)

UPDATE T
SET Col2 = D.Col2
FROM
@tbl1 AS T
LEFT OUTER JOIN
(
SELECT
A.ID,
A.Col1,
B.Col1 + '-' + C.Col1 AS Col2
FROM
Table1 AS A
INNER JOIN
Table1 AS B
ON
(
B.RowNum = A.RowNum
OR B.RowNum + 1 = A.RowNum
OR B.RowNum + 2 = A.RowNum
)
AND B.RowNum % 3 = 1
INNER JOIN
Table1 AS C
ON
(
C.RowNum = A.RowNum
OR C.RowNum - 1 = A.RowNum
OR C.RowNum - 2 = A.RowNum
)
AND C.RowNum % 3 = 0
) AS D
ON T.ID = D.ID

select * from @tbl1
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-11 : 13:24:26

What doesn't work about it? It works just fine for me.

To help your performance add a primary key or a custered index to the table. For example:

Declare @tbl1 table 
(ID INT PRIMARY KEY,
Col1 varchar(50),
Col2 varchar(50)
)

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-09-11 : 16:21:21

I want to give the range each 50 records.

Where do i have to change?

Declare @tbl1 table
(ID INT,
Col1 varchar(50),
Col2 varchar(50)
)
DECLARE @Counter Int
DECLARE @Col1 varchar(50)
SET @Counter = 1
WHILE @Counter < =500
BEGIN
SET @Col1= 'A'+ CONVERT(VarChar(10), @Counter)
INSERT INTO @tbl1 (id,Col1,Col2) VALUES (@Counter,@Col1,NULL)

SET @Counter = @Counter + 1
END

Update @tbl1 Set Col1=Null Where ID in (5,22,10,33,199,223,300,403)
;WITH Table1
AS
(
SELECT
ID, Col1,
ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM
@tbl1
WHERE
Col1 IS NOT NULL
OR Col2 IS NOT NULL
)

UPDATE T
SET Col2 = D.Col2
FROM
@tbl1 AS T
LEFT OUTER JOIN
(
SELECT
A.ID,
A.Col1,
B.Col1 + '-' + C.Col1 AS Col2
FROM
Table1 AS A
INNER JOIN
Table1 AS B
ON
(
B.RowNum = A.RowNum
OR B.RowNum + 1 = A.RowNum
OR B.RowNum + 2 = A.RowNum
)
AND B.RowNum % 100 = 1
INNER JOIN
Table1 AS C
ON
(
C.RowNum = A.RowNum
OR C.RowNum - 1 = A.RowNum
OR C.RowNum - 2 = A.RowNum
)
AND C.RowNum % 100 = 0
) AS D
ON T.ID = D.ID

select * from @tbl1
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-11 : 16:41:05

quote:
Originally posted by rudba

I want to give the range each 50 records.

Where do i have to change?
<snip>

Jobs?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources