?
Solved

Update statement on inner join

Posted on 2014-02-06
6
Medium Priority
?
429 Views
Last Modified: 2014-02-06
Can someone help with this? i am getting 00871 missing SET keyword

UPDATE CREDIT_DETAIL INNER JOIN CREDIT_HEADER ON CREDIT_HEADER.CID = CREDIT_DETAIL.CID
SET ITMPRC = 2.59 
WHERE CREDIT_HEADER.CUSTOMER = 26187 AND  CREDIT_HEADER.CHAIN = 0 AND CREDIT_DETAIL.ITEM = 60200 
;

Open in new window

0
Comment
Question by:FutureDBA-
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39840353
what is the update supposed to be doing?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39840357
assuming you're trying to update the details for a certain subset of headers

try this..


UPDATE credit_detail
   SET itmprc = 2.59
 WHERE EXISTS
           (SELECT NULL
              FROM credit_header
             WHERE credit_header.customer = 26187
               AND credit_header.chain = 0
               AND credit_detail.item = 60200
               AND credit_header.cid = credit_detail.cid);
0
 

Author Comment

by:FutureDBA-
ID: 39840358
I want to update ITMPRC on credit_detail where credit_header customers and chain match a variable in a where clause, linking the 2 cia their primary keys "cid" on each table
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39840361
do you mean like I did in my second post?
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39840363
You may want to see if this article helps you with what you're trying to do:

http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

It covers Oracle, SQL Server and MySQL but gives 3 different ways of performing an update with Join in Oracle.
0
 

Author Closing Comment

by:FutureDBA-
ID: 39840984
Worked perfectly.  Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

621 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