Solved

update two tables in single query oracle

Posted on 2013-10-24
9
716 Views
Last Modified: 2013-12-05
Hi,

I have 2 tables, dp_cardprofile and dp_simapplication. Both tables have the same identifier, DPCP_ID and they have a foreign key relation between them.

I need to update this dpcp_id in both tables, but if I update in just one, I receive an error.

ORA-02291: integrity constraint (DPOWNER.DP_SIMAPPLICATION_FK_DP_CARDPR)
violated - parent key not found

How can I do this update in DPCP_ID in Oracle 10?


desc dp_cardprofile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DPCP_ID                                   NOT NULL NUMBER(38)
 DPCP_NAME                                 NOT NULL VARCHAR2(50)


desc dp_simapplication
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DPSA_ID                                   NOT NULL NUMBER(38)
 DPCP_ID                                   NOT NULL NUMBER(38)
0
Comment
Question by:joaotelles
[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
  • 4
  • 3
9 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 400 total points
ID: 39597047
The need to change PKs indicate a "weak" DB design/pattern... Re-think what you are doing ;-)

To your problem: take a look here

In Oracle there is no 'on update cascade', but there is a workaround (you'll have to change your FK to a deferrable constraint):

http://www.orafaq.com/wiki/Foreign_key#ON_UPDATE_CASCADE

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 39597048
Don't know the Oracle answer, but since you also posted in the SQL Server zone, the SQL Server answer is:

Given that the FK value between the two tables is known.
You CAN'T update multiple tables at the same time.  
What you CAN do is create a transaction that updates two tables with two separate UPDATE statements.  That way, both updates either succeed or fail together.
BEGIN TRAN tr
   BEGIN TRY
       UPDATE #1 goes here
       UPDATE #2 goes here

       COMMIT TRAN tr    -- If code execution makes it here, we're all good, so commit
   END TRY

   BEGIN CATCH
       ROLLBACK TRAN tr   -- If code execution makes it here, an error occured, so kill the transaction
   END CATCH

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598250
so, this was a "SQL Server" question?!?

In Oracle you can update 2 or more tables within 1 statement...
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39601252
@EE admins

SQL Server????????

ORA-02291: integrity constraint (DPOWNER.DP_SIMAPPLICATION_FK_DP_CARDPR)
violated - parent key not found

How can I do this update in DPCP_ID in Oracle 10?


desc dp_cardprofile
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DPCP_ID                                   NOT NULL NUMBER(38)
 DPCP_NAME                                 NOT NULL VARCHAR2(50)

SQL Server doesn't know ORA errors or VARCHAR2 data types, neither the code provided above!!!!

So, where the heck is the connection (apart from awarding a solution that totally differs from the origin subject)?!?!?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39601287
Relax dude.  

The asker included the SQL Server zone in this question, so it's appropriate to give a SQL answer, although I didn't expect it to get full points as the question had Oracle-specific language.

I would think though that everything else being equal an Oracle-specific answer would be more relevant than a SQL Server-specific answer for this question.   So .. it seems appropriate that the asker should chime in on that.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39601419
I didn't mean to bother anyone, so sorry for my offensive reaction ;-)
Moreover, I am not that points addicted, I just wanted to state that something was kind of wrong here...
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39601455
It's a valid point.
0
 

Author Closing Comment

by:joaotelles
ID: 39698841
tks and sry the zone confusion.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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