Update Query by Selecting First Record

Question

The following query works fine if there is only one record in TableC for a CustID. But, in my case, there are multiple records for a CustID in Table C.
How do I fix this query so it would take the 1st record for each CustID ?

update a
 set a.CustCity =c.CustCity
 from TableA a
 inner join TableB b on a.CustNumber=b.CustNumber
 inner join TableC c on b.CustID=c.CustNumber

Answer 1

UPDATE a
SET    a.CustCity = c.CustCity
FROM   TableA a
JOIN   TableB b ON a.CustNumber = b.CustNumber
CROSS APPLY (SELECT TOP (1) c.CustCity
             FROM   TableC c
             WHERE c.CustNumber = b.CustId
             ORDER BY <whatever condition that defines "first" for you>) AS c

Answer 2

Hi @Malam Malam ,

Please refer below query and define the order by yourself according to your requirement:

;with cte as (  
select *,ROW_NUMBER() OVER (PARTITION BY Custnumber   
order by Custnumber,custaddress --define your order column here  
) rn  
FROM TableC)  
update a  
set a.CustCity =c.CustCity  
from TableA a  
inner join TableB b on a.CustNumber=b.CustNumber  
inner join cte c on b.CustID=c.CustNumber  
where c.rn=1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.