How to update a one row where join returns more than one row

Transact-SQL
1

I have a query where its update but its doing the inner join with few other tables however the joins returns more than one row so system is taking by default top row and updating that details in main table; where I would like to do the order by based on certain fields before it does the update.

Update A set a.accntid = case(when x.isComp = 'Y' then aa.aacntid else a.id end)
from A inner join PI on a.pid = PI.pid
inner join A aa on a.fid = aa.fid
cross apply (select isComp from dbo.FnXYZ(PI.pid, aa.fid,a.fid) x

The problem here is, if we have one record for given aa.fid then its fine; if the resultset is more than that then its always take the records has isComp = 'N' (assume if we have two records for this result and one record si 'Y' and othe is with 'N' then its always selects 'N' record by default)

I wanted to select Y record first if it exists if no Y record then any 'N' record is fine. not sure where to write order by for this;

Note: i have done this using CTE but looking for other alternatives as CTE sometimes takes hit on performance.

2

This perhaps?

Update A
set a.accntid = case(when x.isComp = 'Y' then aa.aacntid else a.id end)
from A 
     inner join PI 
        on PI.pid = a.pid
CROSS APPLY 
(
    SELECT TOP 1 fid, aacntid
    FROM A AS aa
    WHERE aa.fid = a.fid
    ORDER BY CASE WHEN isComp = 'Y' THEN 1 ELSE 2 END
             , ... more tie-break columns as necessary ...
) AS aa
cross apply
(
    select isComp 
    from dbo.FnXYZ(PI.pid, aa.fid,a.fid)
) AS x
3

But IsComp column is coming from table:x [cross apply] then can I still use it in CROSS APPLY of "AA" table?

4

Sorry, no.

But your X cross-apply has parameters aa.fid and a.fid but they are the same? i.e. your original code has

inner join A aa on a.fid = aa.fid

so can you move the X cross-apply above the AA one, and use

from dbo.FnXYZ(PI.pid, a.fid,a.fid)

instead?