March 11, 2012 at 5:23 pm
I have a table with two columns (groupnumber, statusbit) and each row is a duplcate with the values groupnumber = 4441 and statusbit null. I want to retrieve the top most record and update it with the value 4441 and then the second with the value 4442, and so on growing through each row till I reach the 4445 update at which point I set a variable back to 4441 and precede to the next 5 records. Currently my cursor only updates every record to the same value based on the last set of the variable. Any suggestions?
Table as is:
groupnumber statusbit
4441 null
...
How I want the table to look after running the update:
groupnumber statusbit
4441 1
4442 1
4443 1
4444 1
4445 1
4441 1
...
HERE IS THE CURSOR I HAVE:
DECLARE @GroupNumber char(11)
DECLARE @Sequence int
SET @Sequence = 4441
DECLARE c1 CURSOR
FOR
SELECT groupnumber
FROM tbl4441
FOR UPDATE OF groupnumber
OPEN c1
FETCH NEXT FROM c1
INTO @GroupNumber
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tbl4441
SET GroupNumber = @Sequence
WHERE Statusbit is null
SET @Sequence = @Sequence + 1
IF @Sequence = 4445
SET @Sequence = 4441
END
CLOSE c1
DEALLOCATE c1
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
March 11, 2012 at 5:43 pm
Doable without a cursor. Look up NTile.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2012 at 7:32 pm
Thank for the suggestion. Found something that looks promising:
UPDATE t
SET t.GroupNumber= 4445, statusbit = 1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY groupnumber) AS Seq,*
From dbo.tbl4441)t
WHERE t.Seq IN (1)
and t.statusbit is null
Note there yet but will continue to work on it
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
March 11, 2012 at 8:20 pm
This appears to work for my purposes:
DECLARE @count int
BEGIN
SET @count = (select count(groupnumber) from dbo.tbl4441 where groupnumber = 4441 and statusbit is null)
WHILE @count <> 0
BEGIN
UPDATE t
SET t.GroupNumber= 4441, statusbit = 1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY groupnumber) AS Seq,*
From dbo.tbl4441)t
WHERE t.Seq IN (1)
and t.statusbit is null
UPDATE t
SET t.GroupNumber= 4442, statusbit = 1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY groupnumber) AS Seq,*
From dbo.tbl4441)t
WHERE t.Seq IN (2)
and t.statusbit is null
UPDATE t
SET t.GroupNumber= 4443, statusbit = 1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY groupnumber) AS Seq,*
From dbo.tbl4441)t
WHERE t.Seq IN (3)
and t.statusbit is null
UPDATE t
SET t.GroupNumber= 4444, statusbit = 1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY groupnumber) AS Seq,*
From dbo.tbl4441)t
WHERE t.Seq IN (4)
and t.statusbit is null
UPDATE t
SET t.GroupNumber= 4445, statusbit = 1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY groupnumber) AS Seq,*
From dbo.tbl4441)t
WHERE t.Seq IN (5)
and t.statusbit is null
SET @count = (select count(groupnumber) from dbo.tbl4441 where groupnumber = 4441 and statusbit is null)
END;
END;
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
March 12, 2012 at 4:24 am
You could likely do it in a single update with ntile.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 6:10 am
You don't need NTILE...
-- create sample table
select top 1000 4441 as groupnumber , cast(null as bit) statusbit
into #t
from syscolumns
-- update
;with cte
as
(
select *,(ROW_NUMBER() OVER (ORDER BY groupnumber)-1) % 5 as pos
from #t
)
update cte
set groupnumber = groupnumber + pos
,statusbit = 1
March 12, 2012 at 3:47 pm
Thanks Eugene, that solution worked great with the update I needed to make:
Here is what ultimately did the trick...
select ID, GroupNumber
into #LogFileTracker
from dbo.logfiletracker
where groupnumber = 4441
and statusbit is null
;with cte
as
(
select *,(ROW_NUMBER() OVER (ORDER BY groupnumber)-1) % 5 as pos
from #logfiletracker
)
update cte
set groupnumber = groupnumber + pos
update dbo.logfiletracker
set groupnumber = l.groupnumber
from dbo.logfiletracker d, #logfiletracker l
where d.ID = l.ID
Eugene Elutin (3/12/2012)
You don't need NTILE...
-- create sample table
select top 1000 4441 as groupnumber , cast(null as bit) statusbit
into #t
from syscolumns
-- update
;with cte
as
(
select *,(ROW_NUMBER() OVER (ORDER BY groupnumber)-1) % 5 as pos
from #t
)
update cte
set groupnumber = groupnumber + pos
,statusbit = 1
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
March 12, 2012 at 4:15 pm
Eugene Elutin (3/12/2012)
You don't need NTILE...
No, but the whole purpose of ntile is to assign rows to one of n sets, which is exactly what the OP wants.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy