用B表更新A表

UPDATE dbo.POR_LOT SET POR_LOT.SHIPPED_FLAG =B.SHIPPED_FLAG
FROM POR_LOT A INNER JOIN ( SELECT T5.LOT_NUMBER,(CASE WHEN t3.TYPE ='生产出库' THEN 0 WHEN T3.TYPE='销售出库' THEN 1 end) SHIPPED_FLAG
FROM WMS_STORAGE_OUT_DETAIL T2
INNER JOIN WMS_STORAGE_OUT T3 ON T2.ORDER_ID = T3.ORDER_ID
INNER JOIN dbo.WIP_CONSIGNMENT T4 ON T2.PALLET_NO=T4.PALLET_NO
INNER JOIN dbo.WIP_CONSIGNMENT_DETAIL T5 ON T4.PALLET_NO=T5.PALLET_NO
WHERE T3.ORDER_NUMBER = 'O002'
) B ON B.LOT_NUMBER=A.LOT_NUMBER

 

 

 

UPDATE t3
SET DataFlag = -1
FROM ( SELECT PRODUCT_ID ,
MAX(CHANGE_DATE) AS CHANGE_DATE
FROM POR_MATERIAL_JK
WHERE DataFlag = 1
GROUP BY PRODUCT_ID
) t1 ,
dbo.POR_MATERIAL_JK t3 ,
@userTable t2
WHERE t2.PRODUCT_ID = t1.PRODUCT_ID
AND t2.PRODUCT_ID = t3.PRODUCT_ID
AND t2.CHANGE_DATE > t1.CHANGE_DATE;