using set vs cursor on update

Last Post 19 Dec 2008 12:52 AM by SwePeso. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ConKi
New Member
New Member

--
18 Dec 2008 10:17 AM
Greeting all,
I have a table that has the following data:
bid gid rev effectivedate row_num
---------------------------------------------------------------------------------------
1 2 0 2001-01-02 00:00:00.000 1
1 1 0 2001-01-04 00:00:00.000 2
1 3 0 2001-01-05 00:00:00.000 3
2 5 0 2001-02-06 00:00:00.000 1
2 4 0 2001-02-07 00:00:00.000 2
3 6 0 2001-03-08 00:00:00.000 1
3 8 0 2001-03-10 00:00:00.000 2
3 7 0 2001-03-11 00:00:00.000 3

As you see, data is grouped by the base id (bid) (1,2,3). Each base id has a number of group id (gid) belongs to it. The row_num col is a calculated one where I used the row_number() over (partition by bid order by effectivedate) as row_num.

Within each base id, I will update the rev of each group id based on the effective date. The data after update will look like this for base id of 1:
1 2 0 2001-01-02 00:00:00.000 1
1 1 1 2001-01-04 00:00:00.000 2
1 3 2 2001-01-05 00:00:00.000 3
and so on for the next base, the rev start over from 0 to ....

I can use cursor to fetch thru each base id and update the rev for each base id, but as you know, it takes 45 min for a roughly 10K baseid.
I am thinking of update using set, but have no clue.

Please help.

Thanks,

ConKi.

I can use cu
ConKi
New Member
New Member

--
18 Dec 2008 10:46 AM
I got it:
Here is the update I used:

UPDATE rev
SET rev = rno - 1
FROM rev AS r
INNER JOIN
(
SELECT bid, gid, ROW_NUMBER() OVER (PARTITION BY bid ORDER BY edate) AS rno FROM rev
)AS r2
ON r2.gid = r.gid

Thanks,

ConKi
SwePeso
New Member
New Member

--
19 Dec 2008 12:52 AM
-- Prepare sample data
DECLARE @Sample TABLE
(
bid INT,
gid INT,
rev INT,
effectivedate DATETIME,
row_num INT
)
INSERT @Sample
SELECT 1, 2, NULL, '2001-01-02', NULL UNION ALL
SELECT 1, 1, NULL, '2001-01-04', NULL UNION ALL
SELECT 1, 3, NULL, '2001-01-05', NULL UNION ALL
SELECT 2, 5, NULL, '2001-02-06', NULL UNION ALL
SELECT 2, 4, NULL, '2001-02-07', NULL UNION ALL
SELECT 3, 6, NULL, '2001-03-08', NULL UNION ALL
SELECT 3, 8, NULL, '2001-03-10', NULL UNION ALL
SELECT 3, 7, NULL, '2001-03-11', NULL

-- Display original data
SELECT *
FROM @Sample

-- Do the dreaded update
UPDATE r
SET row_num = rno,
rev = rno - 1
FROM (
SELECT row_num,
rev,
ROW_NUMBER() OVER (PARTITION BY bid ORDER BY effectivedate) AS rno
FROM @Sample
) AS r

-- Show final data
SELECT *
FROM @Sample
You are not authorized to post a reply.

Acceptable Use Policy