I have 3 columns to base my JOIN on -> ID, Account, Cust. There can be multiple rows containing the same ID value.
I want to prioritise my JOIN on 1) ID, 2) Account, 3) Cust.
So in the example below, the UserCode that should be populated in @UserData should be 'u11z' as all columns contain a value.
How do I do this? Below my code to date...
UPDATE @UserData
SET UserCode = ur.UserCode
FROM @UserData uA
INNER JOIN UserReference ur
ON uA.ID = ur.ID
AND ((ua.Account = ur.Account) OR (ur.Account = ur.Account))
AND ((ua.Cust = ur.Cust) OR (ur.Cust = ur.Cust))
UserReference TABLE:
Cust Account ID UserCode
234 NULL 9A2346 u12x
234 Test 9A2346 u11z
NULL NULL 9A2346 u30s
@UserData TABLE:
Cust Account ID UserCode
234 Test 9A2346 NULL
Thanks!
You can try the following. I joined tables, counted the number of matches, and ranked them. Then select rank 1.
; with userCte (userCodeA, userCodeB, rank)
as
(
select a.usercode, b.usercode,
rank() over (partition by a.id order by case when a.cust = b.cust then 1 else 0 end +
case when a.account = b.account then 1 else 0 end +
case when a.id = b.id then 1 else 0 end desc) as rank
from userdata a
join userreference b
on a.id = b.id or a.account = b.account or a.id = b.id
)
select * from userCte
--update userCte
--set userCodeA = userCodeB
where rank = 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments