Update Query by Selecting First Record

Malam Malam 121 Reputation points
2021-03-28T06:52:08.183+00:00

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
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2021-03-28T10:06:18.147+00:00
    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
    
    No comments

  2. MelissaMa-MSFT 24,116 Reputation points
    2021-03-29T02:26:17.52+00:00

    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.

    No comments