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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply