Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1272
  • Last Modified:

update two tables in single query oracle

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
joaotelles
Asked:
joaotelles
  • 4
  • 3
2 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
so, this was a "SQL Server" question?!?

In Oracle you can update 2 or more tables within 1 statement...
0
Industry Leaders: 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!

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
It's a valid point.
0
 
joaotellesAuthor Commented:
tks and sry the zone confusion.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now