CODEWITHSUNDEEP
mysqlsqldatabasesql-updatesql-order-by
AK-
AK-

Reputation: 15

How to use Order by in Update statement

    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?

Upvotes: 0

Views: 326

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

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 );

Upvotes: 1

Related Questions