?
Solved

Issue with updating using a join

Posted on 2014-12-04
5
Medium Priority
?
65 Views
Last Modified: 2014-12-07
UPDATE DIST_CUSTOMER_PRODUCT_ADJUSTMENT 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'.
0
Comment
Question by:Sanjeet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 58

Expert Comment

by:HainKurt
ID: 40480951
maybe this

UPDATE DIST_CUSTOMER_PRODUCT_ADJUSTMENT
SET D.ADJUSTMENT_AMOUNT = 3.0, D.Adjustment = 0.00
from DIST_CUSTOMER_PRODUCT_ADJUSTMENT d 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 %')
0
 
LVL 58

Expert Comment

by:HainKurt
ID: 40480963
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 %'

Open in new window

0
 

Author Comment

by:Sanjeet
ID: 40480967
Got this error message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "D.ADJUSTMENT_AMOUNT" could not be bound.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40480971
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 %')

Open in new window

0
 
LVL 58

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 40480974
try removing alias

UPDATE DIST_CUSTOMER_PRODUCT_ADJUSTMENT
   SET ADJUSTMENT_AMOUNT = 3.0, 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 %'

Open in new window


or if qer above gives error. try this:

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 and STP.TypePrefix = '0336'
       inner join setup_customer_Code c on D.CustomerID=c.Customer_Code_id and customer_desc LIKE 'HCA %'

Open in new window

0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question