February 11, 2019 at 2:04 pm
Hi there,
I have to determine which value will update to column from multiple values.
see below code
CREATE TABLE #PERSON
(PersonID Varchar(10),
Value int)
Insert into #PERSON values ('000176253',0)
CREATE TABLE #StgPERSON
(PersonID Varchar(10),
Value int)
Insert into #StgPERSON values ('000176253',304)
Insert into #StgPERSON values ('000176253',312)
update p
set p.Value = sp.Value
from #PERSON p
join #StgPERSON sp
on p.PersonID = sp.PersonID
select * from #PERSON
select * from #StgPERSON
every times it update #PERSON values to 304, not 312.
so my question is, if there is scenario like this, it will always update the lower values from multiple?
Please help me to understand this.
Thanks
February 11, 2019 at 2:27 pm
No. It's random which you get if there are multiple matches. If you want a specific match, write your own code to do that. For example:
update p
set p.Value = sp.Value
from #PERSON p
inner join (
select PersonID, MIN(Value) AS Value /* or MAX(Value) */
from #StgPERSON
group by PersonID
) as sp
on p.PersonID = sp.PersonID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 11, 2019 at 2:42 pm
As with a SELECT, if there is no ORDER BY, you can't control which row comes first or last. In this case, you could get either row.
Scott has noted that if you need this to work in a specific way, you need to code to handle that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply