How to use Order by in Update statement

Question

    AccountHmy|UserHmy|Closing|Reason|              |TimeStamp
    606       |299    |0      |Re-opened for Testing |2015-08-11 10:22:36
    606       |108    |1      |Testing Completed     |2015-08-10 10:22:36
    606       |108    |1      |Re-opened for Testing |2015-08-10 10:15:36

I have a SQL query which results me with 4 rows,

      select * from  Synergy.dbo.cu_Close_Account_Log SCAL 
      where SCAL.AccountHmy='602634' order by SCAL.Timestamp desc

Now I need to update another table by joining the previous table, I should only consider the 1st value retuned in the earlier result set.

    UPDATE 
            ST 
        SET 
            StatusHmy = (
                            CASE
                                WHEN ST.StatusHmy = 4 AND SCAL.Closing = 0 THEN 5
                                WHEN ST.StatusHmy = 4 AND SCAL.Closing = 1 THEN 2
                            END                          
                        )
        FROM    
            Synergy.dbo.cu_Transition ST
            LEFT JOIN Synergy.dbo.cu_Close_Account_Log SCAL ON SCAL.AccountHmy = ST.AccountHmy
        WHERE
            ST.StatusHmy = 4 AND SCAL.Closing IN ( 0,1 )
        ORDER BY 
            SCAL.Timestamp desc

But here the Order By is throwing an error. How to solve this?

Why do you think you want an ORDER BY on your UPDATE statement? — Tab Alleman, Aug 11 '15 at 17:45
Your syntax looks like SQL Server syntax. Please tag your question appropriately. — Gordon Linoff, Aug 11 '15 at 17:45
I have to update the First row returned by the first table, So I am using Order by to get that row, Correct me if I'm wrong. — AK-, Aug 11 '15 at 17:46
I have to update another table (cu_transition) by considering only the first value returned by Close_Account table . — AK-, Aug 11 '15 at 17:53
@Hogan, Order by can be used in an update (mysql), see my answer here from today http://stackoverflow.com/a/31939512/1816093 But I think the `mysql` tag made it's way in Rev1 of this question by mistake. — Drew, Aug 11 '15 at 18:23

Gordon Linoff · Accepted Answer · 2015-08-11T21:19:54.297

If you just want to use the subquery for one value, then use TOP and ORDER BY. This can go in a subquery:

UPDATE ST 
    SET StatusHmy = (CASE WHEN ST.StatusHmy = 4 AND SCAL.Closing = 0 THEN 5
                          WHEN ST.StatusHmy = 4 AND SCAL.Closing = 1 THEN 2
                     END )
    FROM Synergy.dbo.cu_Transition ST JOIN
         (select top 1 scal.*
          from  Synergy.dbo.cu_Close_Account_Log SCAL 
          where SCAL.AccountHmy = '602634' 
          order by SCAL.Timestamp desc
         ) scal
         ON SCAL.AccountHmy = ST.AccountHmy
     WHERE ST.StatusHmy = 4 AND SCAL.Closing IN ( 0,1 );

Thanks Gordan, a little modification required for my change, But you gave me how to handle. — AK-, Aug 11 '15 at 20:58

How to use Order by in Update statement

1 Answers1