using row_number in update query

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)

using row_number in update query

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 07:27:56

[code]
DECLARE @TABLE1 TABLE
(
COL1 int,
COL2 int,
COL3 int,
NEW bit,
LATEST bit
)
INSERT INTO @TABLE1
SELECT 1029, 1, 10, 1, 1 UNION ALL
SELECT 1029, 2, 10, 1, 1 UNION ALL
SELECT 1029, 2, 50, 1, 1 UNION ALL
SELECT 1029, 3, 30, 1, 1 UNION ALL
SELECT 1029, 3, 10, 1, 1 UNION ALL
SELECT 1029, 6, 20, 1, 1 UNION ALL
SELECT 2415, 1, 50, 1, 1 UNION ALL
SELECT 2415, 2, 60, 1, 1 UNION ALL
SELECT 2415, 3, 50, 1, 1 UNION ALL
SELECT 2415, 3, 60, 1, 1 UNION ALL
SELECT 2415, 3, 10, 1, 1 UNION ALL
SELECT 2415, 6, 10, 1, 1 UNION ALL
SELECT 6289, 1, 90, 1, 1 UNION ALL
SELECT 6289, 1, 85, 1, 1 UNION ALL
SELECT 6289, 1, 21, 1, 1 UNION ALL
SELECT 6289, 2, 15, 1, 1 UNION ALL
SELECT 6289, 3, 15, 1, 1 UNION ALL
SELECT 6289, 6, 15, 1, 1

;update @TABLE1
set LATEST = 0
where COL1 in
(SELECT COL1 FROM
(SELECT row_number() over (partition by COL1 order by COL1,COL2,COL3) rn ,COL1
from @TABLE1) x where rn = 1)

;update @TABLE1
set NEW = 0
where COL1 in
(SELECT COL1 FROM
(SELECT row_number() over (partition by COL1 order by COL1,COL2,COL3) rn ,COL1
from @TABLE1) x where rn = 6)

;update @TABLE1
set LATEST = 0 , NEW = 0
where COL1 in
(SELECT COL1 FROM
(SELECT row_number() over (partition by COL1 order by COL1,COL2,COL3) rn ,COL1
from @TABLE1) x where rn <> 1 and rn <> 6)

;select * from @table1 order by COL1, COL2

Expected output:

1029 1 10 1 0
1029 2 10 0 0
1029 2 50 0 0
1029 3 30 0 0
1029 3 10 0 0
1029 6 20 0 1

2415 1 50 1 0
2415 2 60 0 0
2415 3 50 0 0
2415 3 60 0 0
2415 3 10 0 0
2415 6 10 0 1

6289 1 90 1 0
6289 1 85 0 0
6289 1 21 0 0
6289 2 15 0 0
6289 3 15 0 0
6289 6 15 0 1

[/code]

value in rn is related to COL2,
I understand that above update query will not update ALL records

please provide me a hint for using row_number in the update query
brief exp: 1029 record in COL1 is repeated 5 times,
for its first occurence, value in NEW column will be 1
for its last occurrence, value in LATEST column will be 1
rest all should be set to 0 using the update queries.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 07:40:16

its seems like what you're trying to do is to set all values to 0.

1st updates sets latest to 0 for rn=1 records
2nd updates sets new to 0 for rn=6
and third sets both to 0 for all others
in effect you're setting all bits to 0. are you sure this is what you intended? i guess not seeing your expected output. please explain your business rules.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 07:43:21

[code]UPDATE t
SET NEW = CASE WHEN new_no = 1 THEN NEW ELSE 0 END,
LATEST = CASE WHEN latest_no = 1 THEN LATEST ELSE 0 END
FROM (
SELECT COL1, COL2, COL3, NEW, LATEST,
new_no = row_number() OVER (PARTITION BY COL1 ORDER BY COL2),
latest_no = row_number() OVER (PARTITION BY COL1 ORDER BY COL2 DESC)
FROM @table1
) t[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 07:51:09

probably this is what you're trying to do:-

DECLARE @TABLE1 TABLE
(
	COL1		int,
	COL2		int,
	COL3		int,
	NEW 		bit,
	LATEST	 	bit
)
INSERT INTO @TABLE1
SELECT	1029,	1, 10, 1, 1	 UNION ALL
SELECT	1029,	2, 10, 1, 1	 UNION ALL	
SELECT	1029,	2, 50, 1, 1	 UNION ALL	
SELECT	1029,	3, 30, 1, 1		UNION ALL
SELECT	1029,	3, 10, 1, 1		UNION ALL
SELECT	1029,	6, 20, 1, 1		UNION ALL
SELECT	2415,	1, 50, 1, 1	  UNION ALL	
SELECT	2415,	2, 60, 1, 1		UNION ALL
SELECT	2415,	3, 50, 1, 1		UNION ALL
SELECT	2415,	3, 60, 1, 1		UNION ALL
SELECT	2415,	3, 10, 1, 1		UNION ALL
SELECT	2415,	6, 10, 1, 1		UNION ALL
SELECT	6289,	1, 90, 1, 1		UNION ALL
SELECT	6289,	1, 85, 1, 1		UNION ALL
SELECT	6289,	1, 21, 1, 1		UNION ALL
SELECT  6289,	2, 15, 1, 1		UNION ALL
SELECT  6289,	3, 15, 1, 1	 UNION ALL	
SELECT  6289,	6, 15, 1, 1	

--select * from @table1 order by COL1, COL2
;update x 
set LATEST = 0  
FROM  
(SELECT row_number() over (partition by COL1 order by COL1,COL2) rn ,* 
from @TABLE1) x 
where rn <> 6

;update x 
set NEW = 0
FROM
(SELECT row_number() over (partition by COL1 order by COL1,COL2) rn ,*
from @TABLE1) x 
where rn <> 1


;select * from @table1 order by COL1, COL2


output
----------------------------------------------------
COL1        COL2        COL3        NEW   LATEST
----------- ----------- ----------- ----- ------
1029        1           10          1     0
1029        2           10          0     0
1029        2           50          0     0
1029        3           30          0     0
1029        3           10          0     0
1029        6           20          0     1
2415        1           50          1     0
2415        2           60          0     0
2415        3           50          0     0
2415        3           60          0     0
2415        3           10          0     0
2415        6           10          0     1
6289        1           90          1     0
6289        1           85          0     0
6289        1           21          0     0
6289        2           15          0     0
6289        3           15          0     0
6289        6           15          0     1

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 08:03:05

quote:
Originally posted by visakh16

its seems like what you're trying to do is to set all values to 0.

1st updates sets latest to 0 for rn=1 records
2nd updates sets new to 0 for rn=6
and third sets both to 0 for all others
in effect you're setting all bits to 0. are you sure this is what you intended? i guess not seeing your expected output. please explain your business rules.

visakh16, You are absolutely correct.
initially BOTH NEW & LATEST columns have bit set as 1.
business rule is:

1029 record in COL1 is repeated 6 times,
for its first occurence, value in LATEST column will be 0
for its last occurrence, value in NEW column will be 0
rest all (i.e. excluding first & last) value in BOTH LATEST & NEW should be set to 0
After the updates,
only first record - NEW
& last record - LATEST will have bit set to 1.

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 09:52:12

thnx khtan & visakh16 for your queries.

however i m facing a performance issue, earlier simple update query (without using row_number) took 46 seconds for 21000 records,
now it takes around 5 MIN for the same records using row_number
Is it possible some other workaround. i m only concerned with the output....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 09:54:15

what are the PK and indexes you have on this table ?

KH
[spoiler]Time is always against us[/spoiler]

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 09:59:21

PK is combination of COL1, COL2 & COL3. Clustered index on the 3 columns.

This update query is part of a cursor that fetches DISTINCT values of COL1 & does the processing on related records of COL2 & COL3.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 10:04:56

You mean you are using CURSOR ? why aren't you use the query I or visakh posted ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 10:04:59

Try like this and see if there's some difference

UPDATE t
SET t.NEW=CASE WHEN b.MinCount<>0 THEN 0 END,
t.LATEST=CASE WHEN c.MaxCount<>0 THEN 0 END
FROM @TABLE t
CROSS APPLY(SELECT COUNT(*) AS MinCount
            FROM @TABLE
            WHERE COL1=t.COL1
            AND COL2<=t.COL2
            AND COL3>t.COL3)b
CROSS APPLY(SELECT COUNT(*) AS MaxCount
            FROM @TABLE
            WHERE COL1=t.COL1
            AND COL2>=t.COL2
            AND COL3<t.COL3)c

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 10:14:39

quote:
Originally posted by khtan

You mean you are using CURSOR ? why aren't you use the query I or visakh posted ?

KH
[spoiler]Time is always against us[/spoiler]

sorry for the trouble, for some few rows, no. of records of COL2 does not match, hence, i have to use something like this


CURSOR FOR COL1 ---
	SELECT distinct(COL1) 
	FROM @Table1 
	ORDER BY COL1

	OPEN COL1_cursor2

	FETCH NEXT FROM COL1_cursor2 
	INTO @COL1

-----------------------------------------
 
	declare @COL2_Count int
	select @COL2_Count = count(COL2)  from @Table1 where COL1 = @COL1

	update x 
	set Latest = 0  
	FROM  
	(SELECT row_number() over (partition by COL1 order by COL1,COL2) rn ,* 
	from @Table1) x 
	where rn <> @COL2_Count and COL1 = @COL1

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-19 : 10:21:27

quote:

Originally posted by visakh16

Try like this and see if there's some difference

UPDATE t
SET t.NEW=CASE WHEN b.MinCount<>0 THEN 0 END,
t.LATEST=CASE WHEN c.MaxCount<>0 THEN 0 END
FROM @TABLE t
CROSS APPLY(SELECT COUNT(*) AS MinCount
            FROM @TABLE
            WHERE COL1=t.COL1
            AND COL2<=t.COL2
            AND COL3>t.COL3)b
CROSS APPLY(SELECT COUNT(*) AS MaxCount
            FROM @TABLE
            WHERE COL1=t.COL1
            AND COL2>=t.COL2
            AND COL3<t.COL3)c

i get the foll. error:
Cannot insert the value NULL into column 'Latest', table '@Table1'; column does not allow nulls. UPDATE fails.

using Comparison of "COL3<t.COL3" wont work because for some values of COL1 and COL2 - COL3 has records in ascending order & some in descending order


DECLARE @TABLE1 TABLE
(
	COL1		int,
	COL2		int,
	COL3		int,
	NEW 		bit,
	LATEST	 	bit
)
INSERT INTO @TABLE1
SELECT	1029,	1, 10, 1, 1	 UNION ALL
SELECT	1029,	2, 10, 1, 1	 UNION ALL	
SELECT	1029,	2, 50, 1, 1	 UNION ALL	
SELECT	1029,	3, 30, 1, 1		UNION ALL
SELECT	1029,	3, 10, 1, 1		UNION ALL
SELECT	1029,	6, 20, 1, 1		UNION ALL
SELECT	2415,	1, 50, 1, 1	  UNION ALL	
SELECT	2415,	2, 60, 1, 1		UNION ALL
SELECT	2415,	3, 50, 1, 1		UNION ALL
SELECT	2415,	3, 60, 1, 1		UNION ALL
SELECT	2415,	3, 10, 1, 1		UNION ALL
SELECT	2415,	6, 10, 1, 1		UNION ALL
SELECT	6289,	1, 90, 1, 1		UNION ALL
SELECT	6289,	1, 85, 1, 1		UNION ALL
SELECT	6289,	1, 21, 1, 1		UNION ALL
SELECT  6289,	2, 15, 1, 1		UNION ALL
SELECT  6289,	3, 15, 1, 1	 UNION ALL	
SELECT  6289,	6, 15, 1, 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 10:27:27

[code]CREATE TABLE #Sample
(
Col1 INT,
Col2 INT,
Col3 INT,
PRIMARY KEY CLUSTERED
(
Col1,
Col2,
Col3
),
New BIT,
Latest BIT
)

INSERT #Sample
SELECT 1029, 1, 10, 1, 1 UNION ALL
SELECT 1029, 2, 10, 1, 1 UNION ALL
SELECT 1029, 2, 50, 1, 1 UNION ALL
SELECT 1029, 3, 30, 1, 1 UNION ALL
SELECT 1029, 3, 10, 1, 1 UNION ALL
SELECT 1029, 6, 20, 1, 1 UNION ALL
SELECT 2415, 1, 50, 1, 1 UNION ALL
SELECT 2415, 2, 60, 1, 1 UNION ALL
SELECT 2415, 3, 50, 1, 1 UNION ALL
SELECT 2415, 3, 60, 1, 1 UNION ALL
SELECT 2415, 3, 10, 1, 1 UNION ALL
SELECT 2415, 6, 10, 1, 1 UNION ALL
SELECT 6289, 1, 90, 1, 1 UNION ALL
SELECT 6289, 1, 85, 1, 1 UNION ALL
SELECT 6289, 1, 21, 1, 1 UNION ALL
SELECT 6289, 2, 15, 1, 1 UNION ALL
SELECT 6289, 3, 15, 1, 1 UNION ALL
SELECT 6289, 6, 15, 1, 1

/*******************************************************************************
Suggestion starts here
*******************************************************************************/
ALTER TABLE #Sample
ADD RowID INT IDENTITY(0, 1)

UPDATE s
SET s.New = CASE WHEN w.RowID = s.RowID AND w.RowType = 'New' THEN 1 ELSE 0 END,
s.Latest = CASE WHEN w.RowID = s.RowID AND w.RowType = 'Latest' THEN 1 ELSE 0 END
FROM #Sample AS s
LEFT JOIN (
SELECT MIN(RowID) AS RowID,
'New' AS RowType
FROM #Sample
GROUP BY Col1

UNION ALL

SELECT MAX(RowID) AS RowID,
'Latest'
FROM #Sample
GROUP BY Col1
) AS w ON w.RowID = s.RowID

ALTER TABLE #Sample
DROP COLUMN RowID
/*******************************************************************************
Suggestion ends here
*******************************************************************************/

SELECT *
FROM #Sample

DROP TABLE #Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-19 : 10:31:36

[code]CREATE TABLE #Sample
(
Col1 INT,
Col2 INT,
Col3 INT,
PRIMARY KEY CLUSTERED
(
Col1,
Col2,
Col3
),
New BIT,
Latest BIT
)

INSERT #Sample
SELECT 1029, 1, 10, 1, 1 UNION ALL
SELECT 1029, 2, 10, 1, 1 UNION ALL
SELECT 1029, 2, 50, 1, 1 UNION ALL
SELECT 1029, 3, 30, 1, 1 UNION ALL
SELECT 1029, 3, 10, 1, 1 UNION ALL
SELECT 1029, 6, 20, 1, 1 UNION ALL
SELECT 2415, 1, 50, 1, 1 UNION ALL
SELECT 2415, 2, 60, 1, 1 UNION ALL
SELECT 2415, 3, 50, 1, 1 UNION ALL
SELECT 2415, 3, 60, 1, 1 UNION ALL
SELECT 2415, 3, 10, 1, 1 UNION ALL
SELECT 2415, 6, 10, 1, 1 UNION ALL
SELECT 6289, 1, 90, 1, 1 UNION ALL
SELECT 6289, 1, 85, 1, 1 UNION ALL
SELECT 6289, 1, 21, 1, 1 UNION ALL
SELECT 6289, 2, 15, 1, 1 UNION ALL
SELECT 6289, 3, 15, 1, 1 UNION ALL
SELECT 6289, 6, 15, 1, 1

/*******************************************************************************
Suggestion 2 starts here
*******************************************************************************/
ALTER TABLE #Sample
ADD RowID INT IDENTITY(0, 1)

UPDATE #Sample
SET New = 0,
Latest = 0
WHERE 1 IN (New, Latest)

UPDATE s
SET s.New = CASE WHEN w.RowID = s.RowID AND w.RowType = 'New' THEN 1 ELSE 0 END,
s.Latest = CASE WHEN w.RowID = s.RowID AND w.RowType = 'Latest' THEN 1 ELSE 0 END
FROM #Sample AS s
INNER JOIN (
SELECT MIN(RowID) AS RowID,
'New' AS RowType
FROM #Sample
GROUP BY Col1

UNION ALL

SELECT MAX(RowID) AS RowID,
'Latest'
FROM #Sample
GROUP BY Col1
) AS w ON w.RowID = s.RowID

ALTER TABLE #Sample
DROP COLUMN RowID
/*******************************************************************************
Suggestion 2 ends here
*******************************************************************************/

SELECT *
FROM #Sample

DROP TABLE #Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources