I was trying to help someone else and ran into a problem myself... I got a basic example working for numbering rows within a grouping and then added an update clause joining to another temporary table I had created that had a count of the maximum number of rows i wanted to update. the problem is that it refuses to stop at the update count I am providing it from the table.
If I change the update to a select, it stops at the correct limit. but when i change to an update it updates all the rows in the table.
If I hardcode the update count limit. It correctly stops at the limit.
What am I doing wrong? To me it seems as if there is a bug in 2008, but surely not! It has to be my understanding....
drop TABLE #tempcount
create TABLE #tempcount (CustomerNo varchar(20), UpdateCount int)
insert into #tempcount (CustomerNo , UpdateCount )
select bk_customer, COUNT(*) - 100 from dbo.bookings
group by bk_customer
order by bk_customer
select * from #tempcount
Select bk_customer as customer
,row_number() over (partition by bk_customer
order by bk_customer)
--set bk_customer = 'C1010444'
from bks_numbered inner join
#tempcount on customer = CustomerNO
where [MyRow] < 125 -- UpdateCount
and customer = CustomerNO