question

MalamMalam-4042 avatar image
0 Votes"
MalamMalam-4042 asked MelissaMa-msft commented

Update Query by Selecting First Record

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
sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Why is your query not good?

Which row is to be considered the first? (How to order rows and take the first one?)

0 Votes 0 ·

Hi @MalamMalam-4042,

Could you please validate all the answers so far and provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MalamMalam-4042,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.