Solved

Update statement on inner join

Posted on 2014-02-06
6
425 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-
[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
  • 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 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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