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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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