Update Statement Issues with Derived Tables

[hungerf5]

I'm trying to resynchronize two tables in our ERP that have gotten out of wack. There are two tables involved: INVENTORY_TRANS which stores detailed inventory transactions; the other table is PART_WAREHOUSE which stores the current balance by WAREHOUSE and PART_ID.

I can't just take the sum of INVENTORY_TRANS.QTY because the numerical data is always positive (not signed). There is TYPE indicator which specifies the direction of the inventory TX.

Below is the update statement I'm trying to Execute:

UPDATE PW
SET AVAILABLE_QTY = IT.QTY
FROM (SELECT PART_ID, AVAILABLE_QTY FROM dbo.PART_WAREHOUSE) PW
JOIN (SELECT PART_ID, SUM(CASE TYPE WHEN 'I' THEN QTY ELSE (QTY * -1)END) AS QTY
FROM dbo.INVENTORY_TRANS GROUP BY PART_ID) IT ON (IT.PART_ID = PW.PART_ID)
SELECT AVAILABLE_QTY FROM PART_WAREHOUSE

Below is the Error message SQL Server (Query Analyzer) is giving me:
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'PW' is not updatable because a column of the derived table is derived or constant.

I've ran Updates off select statements before and everything has worked as expected. To me it sound like that case statement is causing problems.

I consider this update similar to the one above (and I know it works properly):
UPDATE PO
SET
TOTAL_AMT_ORDERED = CALC_TOT_AMT_ORDERED,
TOTAL_AMT_RECVD = CALC_TOT_AMT_RECVD
FROM PURCHASE_ORDER PO JOIN
(SELECT
POL.PURC_ORDER_ID,
ROUND(SUM(POL.ORDER_QTY * POL.UNIT_PRICE), 2) AS CALC_TOT_AMT_ORDERED,
ROUND(SUM(POL.TOTAL_RECEIVED_QTY * POL.UNIT_PRICE), 2) AS CALC_TOT_AMT_RECVD
FROM dbo.PURC_ORDER_LINE POL
GROUP BY
POL.PURC_ORDER_ID) POT ON (PO.ID = POT.PURC_ORDER_ID)
SELECT * FROM PURCHASE_ORDER

Any suggestions on what I'm doing wrong?

--Rick

 

[fluteplr]

There are not equivelent.

In the 2nd the table your are updating is not based on a select statement, in the 1st one it is.

Try


UPDATE PW
SET AVAILABLE_QTY = IT.QTY
FROM
part_warehouse pw
JOIN (SELECT PART_ID, SUM(CASE TYPE WHEN 'I' THEN QTY ELSE (QTY * -1)END) AS QTY
FROM dbo.INVENTORY_TRANS GROUP BY PART_ID) IT ON (IT.PART_ID = PW.PART_ID)
SELECT AVAILABLE_QTY FROM PART_WAREHOUSE