Solved

Update statement on inner join

Posted on 2014-02-06
6
426 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Suggested Courses

636 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