Problem Updating Based of of Row_Number Value

SJCFL-Admin
SJCFL-Admin used Ask the Experts™
on
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
go
create TABLE  #tempcount (CustomerNo varchar(20), UpdateCount int)
go

insert into #tempcount (CustomerNo , UpdateCount )
select bk_customer, COUNT(*) - 100 from dbo.bookings
group by bk_customer
order by  bk_customer
go
select * from #tempcount
;
with bks_numbered
as
(
Select bk_customer as customer
 ,row_number() over (partition by  bk_customer
                         order by  bk_customer)
                               AS 'MyRow'                          
FROM   dbo.bookings
)
--update dbo.bookings
--set bk_customer = 'C1010444'
select *
from bks_numbered inner join
#tempcount on customer = CustomerNO
where [MyRow] < 125 -- UpdateCount
and customer = CustomerNO

;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can you try this update?

;with bks_numbered
as
(
Select bk_customer as customer
 ,row_number() over (partition by  bk_customer
                         order by  bk_customer)
                               AS 'MyRow'                          
FROM   dbo.bookings 
)
update a
set a.customer = 'C1010444'
from bks_numbered a inner join 
#tempcount b on a.customer = b.CustomerNO and a.[MyRow] < b.UpdateCount

Open in new window

Author

Commented:
ralmada,

You are my Number 1 HERO !!!
Top Expert 2012

Commented:
You can also do it with a derived table. Let me know if you are interested.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial