sarepta sarepta - 3 months ago 9
SQL Question

update assignment applied to set vs row

I was trying to randomly update stateid in [address], so i started with this query

update [address]
set stateid = (select top 1 id
from lookupvalue
where lookuptypeid = 3 and code = 1
order by newid()),
countryid = 1

select *
from [address]


but as it appears, all the rows get the same value, when I tried referencing [address] table from the inner select query, the update is ran per row (and I got the desired effect).

update [address]
set stateid = (select top 1 id
from lookupvalue
where lookuptypeid = 3 and [address].id = [address].id
and code = 1
order by newid()),
countryid = 1

select *
from [address]


Can someone elaborate on the above behavior, is it related to query plan, do I have to make a dummy reference in the inner select to force the update assignment to be evaluated per row?

Answer

This is too long for a comment.

Yes, it is related to the query plan. SQL Server (and other databases too) see the subquery and decide that it can be optimized away. I consider this an error because newid() is a volatile function, so the subquery cannot be optimized away. But, there are arguments on the other side as well.

Putting in the outer reference fixes the problem, so you know how to get around this "optimization".

Comments