Update query with correlated subquery can't go through table alias

Question

I have this update query on my SQL Server

UPDATE BK_TT_DELIVERY_PLAN TDP
SET (PACK_TYPE) = 
            (SELECT OPD.PACKING_TYPE
               FROM TT_OM_PACK_DISCREPANCY OPD
              WHERE ISNULL(TDP.PO_REF,TDP.PO_NUMBER) = OPD.PO_NUMBER
                    AND TDP.PO_ITEM = ODP.PO_ITEM)
WHERE 
    EXISTS (SELECT 1
            FROM TT_OM_PACK_DISCREPANCY OPD
            WHERE ISNULL(TDP.PO_REF,TDP.PO_NUMBER) = OPD.PO_NUMBER
              AND TDP.PO_ITEM = ODP.PO_ITEM) 

When I try to execute I always get this error:

Lookup Error - SQL Server Database Error: Incorrect syntax near 'TDP'

I try it on Oracle - it works fine.

Any workaround for this issue?

Thank you.

Answer 1

You don't alias a table in the UPDATE clause, you do so in the FROM. As there is not FROM in this statement, then you do not give the table an alias. This would, however, mean that you would have to change all the aliases in the query to the table's name:

UPDATE dbo.BK_TT_DELIVERY_PLAN
   SET (PACK_TYPE) = 
            (SELECT OPD.PACKING_TYPE
               FROM TT_OM_PACK_DISCREPANCY OPD
              WHERE ISNULL(BK_TT_DELIVERY_PLAN.PO_REF,BK_TT_DELIVERY_PLAN.PO_NUMBER) = OPD.PO_NUMBER
                    AND BK_TT_DELIVERY_PLAN.PO_ITEM = ODP.PO_ITEM)
 WHERE EXISTS
      (SELECT 1
         FROM TT_OM_PACK_DISCREPANCY OPD
              WHERE ISNULL(BK_TT_DELIVERY_PLAN.PO_REF,BK_TT_DELIVERY_PLAN.PO_NUMBER) = OPD.PO_NUMBER
                    AND BK_TT_DELIVERY_PLAN.PO_ITEM = ODP.PO_ITEM);

Therefore using a FROM is likely a better idea, and then you reference the alias in the UPDATE clause:

UPDATE TDP
   SET (PACK_TYPE) = 
            (SELECT OPD.PACKING_TYPE
               FROM TT_OM_PACK_DISCREPANCY OPD
              WHERE ISNULL(TDP.PO_REF,TDP.PO_NUMBER) = OPD.PO_NUMBER
                    AND TDP.PO_ITEM = ODP.PO_ITEM)
FROM dbo.BK_TT_DELIVERY_PLAN TDP
WHERE EXISTS
      (SELECT 1
         FROM TT_OM_PACK_DISCREPANCY OPD
              WHERE ISNULL(TDP.PO_REF,TDP.PO_NUMBER) = OPD.PO_NUMBER
                    AND TDP.PO_ITEM = OPD.PO_ITEM);

---

Separate note, you shouldn't use ISNULL is the WHERE (or ON), as it causes the query to become non-SARGable. Use proper boolean logic. It also seems, as well, that this could be made far smaller with a JOIN and not 2 subqueries.

UPDATE TDP
SET PACK_TYPE = OPD.PACKING_TYPE
FROM dbo.BK_TT_DELIVERY_PLAN TDP
     JOIN TT_OM_PACK_DISCREPANCY OPD ON (TDP.PO_REF = OPD.PO_NUMBER 
                                     OR  (TDP.PO_REF IS NULL AND TDP.PO_NUMBER = OPD.PO_NUMBER))
                                    AND TDP.PO_ITEM = OPD.PO_ITEM;