Update statement with "case syntax" is unable to update a specific column in temp table With reviewing a stored procedure for a current SSRS migration. I noticed the case syntax listed below for the temp table (#tempShip) is not taking affect for the column "ord_status" in the temp table. The stored procedure that collects data for the temp table is rather massive so I will refrain from posting. But I believe with the code provided for the update it may do some justification. 

Questions:


Can you utilize a case statement on a temp table as an update?
Is there another method I should try besides the case statement? (I don't think using a subquery with "IF" or "WHERE EXISTS with a select statement" would aid in this)


Notes:

***Stored procedure executes without any errors and temp table is populated with data as expected (But update with case statement does not have expected results)

***With doing a simple case statement as seen below the translation occurs fine

Basic query to test case translation of ord_status data in temp table. Output is correct

select ord_status,
 CASE
     WHEN RTRIM([ord_status]) = 'N' THEN 'New'
     WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
     WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
     ELSE ''
 END
 from #tempShip


***End of stored procedure where temp table is being updated

UPDATE  [#tempShip] SET
        [#tempShip].[DUP] = 1,
        [#tempShip].[ord_status] = 
           CASE
                 WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                 WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                 WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                 ELSE ''
           END

        FROM #tempShip
         INNER JOIN
    (
        SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
        FROM #tempShip AS X
        GROUP BY X.order_N_ext
    ) AS Y
         ON #tempShip.order_N_ext = Y.order_N_ext
    WHERE Y.dup &gt; 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type 
    SELECT * FROM #tempShip 
    WHERE ship_type LIKE @ship_type 
    -------&lt;&lt;&lt;


END
DROP TABLE #tempShip


**order_N_ext

SELECT O.order_no
    , O.ext
    , CONVERT(varchar(20), O.order_no) + ' - ' + CONVERT(varchar(2), O.ext) 
AS 'order_N_ext'


Data Example:

Ord_Num  BD order_N_ext Loc     Cust        Ship_date                      Ord_Status
1252868  1  1252868 - 1 RAS HQ  21115   2017-04-19 00:00:00.000 2570.63    P       
1253995  1  1253995 - 1 RAS HQ  11471   2017-04-19 00:00:00.000 1270.45    Q       
1254526  0  1254526 - 0 RAS HQ  24104   2017-04-19 00:00:00.000 2214.67    P
Here is the solution: (The update statements had to be rearranged so they executed on there own.) 

UPDATE  [#tempShip] SET  [ord_status] = CASE
                WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                ELSE ''
                END

UPDATE                 [#tempShip] SET           [#tempShip].[DUP] = 1
FROM #tempShip
INNER JOIN
(
SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
FROM #tempShip AS X
GROUP BY X.order_N_ext
) AS Y
ON #tempShip.order_N_ext = Y.order_N_ext
WHERE Y.dup &gt; 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type 
    SELECT * FROM #tempShip 
    WHERE ship_type LIKE @ship_type 
    -------&lt;&lt;&lt;