Update records in table with CTE in join

Question

I've been looking at this update statement that includes a join to a cte for a while. Not able to figure out the problem. It's giving me: "Syntax error: unexpected 'UPDATE'. (line 7)". Not sure what I'm missing. Thanks!

WITH SALESORDER_CTE AS 
(
    SELECT TO_TIMESTAMP(ORDERITEMDRUGMODIFIED, 'MM/DD/YYYY HH12:MI:SS PM') AS CUSTBODY_ORDERITEMDRUGMODIFIED, *, 
        ROW_NUMBER() OVER (PARTITION BY TRANID ORDER BY ORDERITEMDRUGMODIFIED DESC) AS RN
    FROM SALESORDER
)
UPDATE a
SET a.ORDERITEMDRUGMODIFIED = b.ORDERITEMDRUGMODIFIED
FROM SALESORDERLINE AS a
INNER JOIN SALESORDER_CTE AS b ON a.TRANSACTION = b.ID
WHERE b.RN = 1
AND b.ID = 5369076;

Answer 1

I see two syntax problems here. First, the join condition is implicit and should appear in the WHERE clause, as ON is not supported. Second, the left hand side of the SET clause should not have an alias. Try this version:

UPDATE a
SET ORDERITEMDRUGMODIFIED = b.ORDERITEMDRUGMODIFIED
FROM SALESORDERLINE AS a
INNER JOIN SALESORDER_CTE AS b
WHERE a.TRANSACTION = b.ID AND
      b.RN = 1 AND
      b.ID = 5369076;