SQL Server Performance Forum – Threads Archive
Multi-Table Field Update Woes
I’ve got a query in Access that run through a table and calculates new cummulative totals week by week for multiple product IDs. Although the syntax looks odd, it works perfectly in Access and only takes about 1 minute to run. UPDATE TEMP_TBLINNER JOIN HOLD_TBL ON (TEMP_TBL.MDL_ID = HOLD_TBL.MDL_ID)
AND (TEMP_TBL.FACTORY = HOLD_TBL.FACTORY)
SET HOLD_TBL.CUM_TTL = TEMP_TBL!SALES_MO + HOLD_TBL!CUM_TTL,
TEMP_BL.CUM_SALES_TTL = TEMP_TBL!RTL_TTL_SLS_MO + HOLD_TBL!CUM_TTL; My problem is that I cannot find a way to do the same thing in SQL Server 2000. Everything I’ve tried takes over 72 hours to run! Is there a quick easy way to update two interelated fields in different table using only one SQL statement?
Try this also UPDATE T SET H.CUM_TTL = T.SALES_MO + H.CUM_TTL,
TEMP_BL.CUM_SALES_TTL = T.RTL_TTL_SLS_MO + H.CUM_TTL
FROM TEMP_TBL T INNER JOIN HOLD_TBL ON (T.MDL_ID = H.MDL_ID)
AND (T.FACTORY = H.FACTORY) Madhivanan Failing to plan is Planning to fail
Unfortunately, addapting the syntax to T-SQL cannot solve this problem since UPDATE can only reference one table. This means that "UPDATE T SET H.CUM_TTL =…" give an error because H.CUM_TTL is not referenced in the UPDATE clause (ie "T").
]]>