November 25, 2005 at 8:35 am
We are looking for a way to update (ex) the 5 first rows of a table depending some selections and a order by clause.
ex (of a non working query)
set rowcount = @arowcount
update t1 set c1 = @something
from t1
where c2 = @sm2
order by c3
anybody has an idea on how to to this
we'v tried with a subquery, select top 1000 from ... order by, byt the problem is that the order of the subquery is sorted, but you have no guarantie that the update will pick the first records of the subquery. And a top x as a variable is also inpossible.
We would prefer not to do it with dynamic sql as this would be to slow.
November 25, 2005 at 9:08 am
Isn't there a primary key in your table?
Can't you do something like:
UPDATE mytable t1
SET name = newname
FROM (SELECT TOP 5 t2.pk
FROM mytable t2
ORDER BY name)
WHERE t1.pk = t2.pk
or use a cursor?
DECLARE curTest CURSOR
FOR SELECT TOP 5 pk FROM mytable ORDER BY name
FOR UPDATE of name
DECLARE @count smallint
SELECT @count = 1
OPEN curTest
FETCH NEXT FROM curTest INTO @pk
WHILE (@count < 6 AND @@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
UPDATE mytable
SET name = something
WHERE pk = @pk
END
FETCH NEXT FROM curTest INTO @pk
SELECT @count = @count + 1
END
CLOSE curTest
DEALLOCATE curTest
GO
November 25, 2005 at 9:09 am
Can you explain a bit about your purpose and intentions here?
If you update 5 rows in one update statement, there is no logical ordering - all 5 rows are logically updated at the same time. Why is ordering an issue for you?
/Kenneth
November 25, 2005 at 9:25 am
The ordering would only be used to select the right records to be updated.
we want to update the next x records, but the selection of these records can be influenced by the where. A simple between currect record and current + 5 can't be used.
The updates of the records to be updated can be some at the same time, that's not the issue.
November 25, 2005 at 9:27 am
Jan, we knwo that with a cursor, or even with dyn amic sql it is possible, but knowing the lost of speed when using cursors we would like to find a solution with normal sql
November 25, 2005 at 9:47 am
As you have discovered, the TOP is not very usefull when what is needed is the RANK. If you are using SQL Server 2005, there is a RANK function but otherwise, see articles by Joe Celko on how to determine the RANK using SQL.
Here is solution that uses an arbitrary algorithm to break rank ties. Note that the RANK will start with zero, so a rank of less than the limit is needed.
use tempdb
go
if exists (select 1 from sysobjects where name = N'PrizeEntry' and uid = user_id(N'dbo') ) drop table PrizeEntry
go
Create table PrizeEntry
(PrizeEntryPk uniqueidentifier not null default newId()
,PrizeEntryTs datetime not null
,WinnerInd Char(1) not null default 'N'
, constraint PrizeEntry_PK primary key (PrizeEntryPk)
, constraint PrizeEntry_C_WinnerInd check (WinnerInd in ('Y','N'))
)
go
create unique index PrizeEntry_X_PrizeEntryTs on PrizeEntry (PrizeEntryTs, PrizeEntryPk)
go
truncate table PrizeEntry
go
set nocount on
go
declare @EntryCnt integer
set @EntryCnt = 0
while @EntryCnt < 100
begin
set @EntryCnt = @EntryCnt + 1
insert into PrizeEntry
(PrizeEntryTs)
select dateadd(hh , @EntryCnt, '2005-01-01')
from sysobjects
end
go
begin transaction
declare @LimitCnt integer
set @LimitCnt = 50
update PrizeEntry
set WinnerInd = 'Y'
where @LimitCnt
> (select count(*) from PrizeEntry as PrizeEntryRank
where PrizeEntryRank.PrizeEntryTs < PrizeEntry.PrizeEntryTs
or ( PrizeEntryRank.PrizeEntryTs = PrizeEntry.PrizeEntryTs
AND PrizeEntryRank.PrizeEntryPk < PrizeEntry.PrizeEntryPk))
select count(*) from PrizeEntry where WinnerInd = 'y'
rollback
SQL = Scarcely Qualifies as a Language
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply