Sanjeet
asked on
Issue with updating using a join
UPDATE DIST_CUSTOMER_PRODUCT_ADJU STMENT D
Join Setup_Tissue_Prefix STP On D.TissuePrefixId = STP.TissuePrefixId
SET D.ADJUSTMENT_AMOUNT = 3.0,
D.Adjustment = 0.00
Where STP.TypePrefix In ('0336')
And D.CustomerID IN (SELECT Customer_Code_id from setup_customer_Code where customer_desc LIKE 'HCA %')
Error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D'.
Join Setup_Tissue_Prefix STP On D.TissuePrefixId = STP.TissuePrefixId
SET D.ADJUSTMENT_AMOUNT = 3.0,
D.Adjustment = 0.00
Where STP.TypePrefix In ('0336')
And D.CustomerID IN (SELECT Customer_Code_id from setup_customer_Code where customer_desc LIKE 'HCA %')
Error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D'.
or better this
UPDATE DIST_CUSTOMER_PRODUCT_ADJUSTMENT
SET D.ADJUSTMENT_AMOUNT = 3.0, D.Adjustment = 0.00
FROM DIST_CUSTOMER_PRODUCT_ADJUSTMENT d
inner Join Setup_Tissue_Prefix STP On D.TissuePrefixId = STP.TissuePrefixId and STP.TypePrefix = '0336'
inner join setup_customer_Code c on D.CustomerID=c.Customer_Code_id and customer_desc LIKE 'HCA %'
ASKER
Got this error message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "D.ADJUSTMENT_AMOUNT" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "D.ADJUSTMENT_AMOUNT" could not be bound.
You can't JOIN directly to an updated table. Here's how you should do it:
UPDATE D
SET D.ADJUSTMENT_AMOUNT = 3.0,
D.Adjustment = 0.00
FROM DIST_CUSTOMER_PRODUCT_ADJUSTMENT D
INNER JOIN Setup_Tissue_Prefix STP On D.TissuePrefixId = STP.TissuePrefixId
WHERE STP.TypePrefix In ('0336')
AND D.CustomerID IN (SELECT Customer_Code_id from setup_customer_Code where customer_desc LIKE 'HCA %')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE DIST_CUSTOMER_PRODUCT_ADJU
SET D.ADJUSTMENT_AMOUNT = 3.0, D.Adjustment = 0.00
from DIST_CUSTOMER_PRODUCT_ADJU
Where STP.TypePrefix In ('0336')
And D.CustomerID IN (SELECT Customer_Code_id from setup_customer_Code where customer_desc LIKE 'HCA %')