Invalid identifier on Update statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Invalid identifier on Update statement

Author

Topic

kylenedic
Starting Member

2 Posts

Posted - 2012-07-20 : 15:59:56

UPDATE XXINV.XXINV_ITEM_SNAP_EGS_E5A6 ISE
SET ISE.ORACLE_ON_HAND = (SELECT SUM (IVD.ON_HAND_QUANTITY)
FROM apps.XXRPT_INV_VALUATION_D535_V IVD
WHERE IVD.ITEM_NUMBER = IVD.ITEM_NUMBER
AND IVD.OWNING_ORG_CODE = IVD.OWNING_ORG_CODE )
WHERE ISE.ITEM_NUMBER = IVD.ITEM_NUMBER
AND ISE.ORGANIZATION_CODE = IVD.OWNING_ORG_CODE

I've been trying to update a table with info from another table and can't get it to work I keep getting an error of invalid identifier for IVD.OWNING_ORG_CODE at the end. Any suggestions?

Ive also tried this
UPDATE XXINV.XXINV_ITEM_SNAP_EGS_E5A6 ISE
SET ISE.ORACLE_ON_HAND = (SELECT SUM (IVD.ON_HAND_QUANTITY)
FROM apps.XXRPT_INV_VALUATION_D535_V IVD
WHERE IVD.ITEM_NUMBER = ISE.ITEM_NUMBER
AND IVD.OWNING_ORG_CODE = ISE.ORGANIZATION_CODE)
WHERE EXISTS ( SELECT SUM (IVD.ON_HAND_QUANTITY)
FROM apps.XXRPT_INV_VALUATION_D535_V IVD
WHERE IVD.ITEM_NUMBER = ISE.ITEM_NUMBER
AND IVD.OWNING_ORG_CODE = ISE.ORGANIZATION_CODE)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-20 : 16:23:41

assuming you are using MS Sql Server 2005 or later try this:


update ise set
       ISE.ORACLE_ON_HAND = ca.on_hand_quantity_sum
from   XXINV.XXINV_ITEM_SNAP_EGS_E5A6 ISE
cross apply (
       select sum(dvd.on_hand_quantity) on_hand_quantity_sum
       from   apps.XXRPT_INV_VALUATION_D535_V IVD
       where  ivd.item_number = ise.item_number
       ) ca

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources