50.000 limit on update query with join

[chironh]

I'm experiencing a very strange behaviour. Maybe it's related to one of the latest updates (I'm using version 9.0.1) because I haven't noticed this before (and I've worked with big datasets in the past).

This query limits to 50.000 records (which is a pretty specific number/cap if you ask me):

update account
set account.accountnumber = msdyn_customerasset.msdyn_name
from account
inner join msdyn_customerasset ON account.accountid = msdyn_customerasset.msdyn_account

whereas the following query has no limit:

update account
set account.accountnumber = null
from account
inner join msdyn_customerasset ON account.accountid = msdyn_customerasset.msdyn_account

To be clear, the dataset should update 57xxx records, which is the result of this query:

select account.accountnumber, msdyn_customerasset.msdyn_name
from account
inner join msdyn_customerasset ON account.accountid = msdyn_customerasset.msdyn_account

[chironh]

FYI: there are no safety limits in place.

edit: I haven't checked the actual number of records updated. I'm basing my comments on the information provided here:

[MarkMpn]

Thanks for the information!

The first query triggers legacy paging which limits the results to 50k. I'll look at improving this in the next update, but in the meantime you can work around it by forcing a calculation in the SET clause, e.g.

update account
set account.accountnumber = msdyn_customerasset.msdyn_name + ''
from account
inner join msdyn_customerasset ON account.accountid = msdyn_customerasset.msdyn_account