Update with TOP 1 SELECT

Question

Hi everyone,

I have 2 tables #TABLE  and #DETAIL_TABLE in that the second table has too many rows for each one of #TABLE's records. I have to update #TABLE by choosing only one value from #DETAIL_TABLE for each one of records in #TABLE (for each field, sorting logic is different). Following query works fine(probably SQL server optimizes it, if any way exists) but I'm not sure if it is the better way of doing it:

UPDATE 

#TABLE SET

COLUMN_VALUE1 = (SELECT TOP 1 a.COLUMN_VALUE1 FROM #DETAIL_TABLE a WHERE a.FOREIGN_KEY = #TABLE.KEY_VALUE ORDER BY a.RANKED_FIELD1),

COLUMN_VALUE2 = (SELECT TOP 1 a.COLUMN_VALUE2 FROM #DETAIL_TABLE a WHERE a.FOREIGN_KEY = #TABLE.KEY_VALUE ORDER BY a.RANKED_FIELD2),

COLUMN_VALUE3 = (SELECT TOP 1 a.COLUMN_VALUE3 FROM #DETAIL_TABLE a WHERE a.FOREIGN_KEY = #TABLE.KEY_VALUE ORDER BY a.RANKED_FIELD3), ...

I prefer not use CURSOR , also do not like to break above query to multiple smaller INNER JOIN updates. Anyone can tell me what is the better way doing above regardingly?

Thanks

Amir

Answer 1

The best way is to make UPDATE FROM

 

 Example :

UPDATE T1 SET T1.COLUMN_VALUE1 = D.COLUMN_VALUE1, T1.COLUMN_VALUE2 = D.COLUMN_VALUE2 ....
FROM #TABLE T1
JOIN  #DETAIL_TABLE D ON T1.KEY_VALUE = D.FOREIGN_KEY
 WHERE add_extra_filter_here

This is it ..the only need is some extra filters and it's ok

                

---

OR Vs XOR

Answer 2

JOIN has 2 issues, I mentioned it in my question, do not want to use update from join,except if it can include all fields:

1- I have to RANK the #TABLE_DETAIL first and then add a condition of RANK = 1 in my INNER JOIN (a supplementary step before join!)

2- There are about 10 fields that update in a single statements, each having a different sort order (MAX or MIN functions are not applicable), so I must use 10 different JOIN queries