locked
Update with TOP 1 SELECT RRS feed

  • 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

    Monday, June 25, 2012 8:38 PM

All replies

  • 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

    Monday, June 25, 2012 9:04 PM
  • 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

    Monday, June 25, 2012 9:34 PM