OraFAQ Forum: SQL & PL/SQL » Simultaneous update in child table

Home » SQL & PL/SQL » SQL & PL/SQL » Simultaneous update in child table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Simultaneous update in child table [message #617807]

Thu, 03 July 2014 14:05

many_yammy
Messages: 4
Registered: July 2014

Junior Member

Hi all,

First I have a question.
Well, assume I have two tables one parent and the other child. First I imported the data into child table and then the parent. Now I am wondering if it is possible to update the foreign key in the child table per each entry to the parent table?(e.g with trigger or ?!)

Well, my case is a bit complicated. So first I need to know if this idea works or not. And hopefully if the respond is yes I need your help friends Smile

.

I appreciate your advice in advance.
Best regards,
Many

Report message to a moderator

Re: Simultaneous update in child table [message #617812 is a reply to message #617807]

Thu, 03 July 2014 14:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

a trigger fires when DML occurs; so I say your solution does not involve a trigger.
I do not believe that any Foreign Key exist now.
How do you, I, or oracle determine or know which child row is associated with which parent row NOW?

Please post CREATE TABLE statements for both tables
Please post sample INSERT statements to populate both tables with test data

Report message to a moderator

Re: Simultaneous update in child table [message #617814 is a reply to message #617807]

Thu, 03 July 2014 14:17

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Are you saying that you want to insert rows into the child table before you insert rows into the parent table? If so, no problem: define the foreign key constraint as deferrable.

Report message to a moderator

Re: Simultaneous update in child table [message #617822 is a reply to message #617812]

Thu, 03 July 2014 15:11

many_yammy
Messages: 4
Registered: July 2014

Junior Member

Ok the whole story as below:

I have three tables:
CITYOBJEC

CREATE TABLE "ANDREAS"."CITYOBJECT" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"CREATION_DATE" DATE, 
	"LAST_MODIFICATION_DATE" DATE, 
	"UPDATING_PERSON" VARCHAR2(256 BYTE), 
	"REASON_FOR_UPDATE" VARCHAR2(4000 BYTE),
	 CONSTRAINT "CITYOBJECT_PK" PRIMARY KEY ("ID"));

CAFM_BUILDING

  CREATE TABLE "ANDREAS"."CAFM_BUILDING" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"BUILDING_ID" VARCHAR2(4000 BYTE), 
	"FK_CITYOBJECT_ID" NUMBER, 
	 CONSTRAINT "CAFM_BUILDING_PK" PRIMARY KEY ("ID"),
	 CONSTRAINT "CAFM_BUILDING_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID")
	  REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE);

CAFM_FACADE

CREATE TABLE "ANDREAS"."CAFM_FACADE" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"FACADE_ID" VARCHAR2(4000 BYTE), 
	"FK_CAFM_BUILDING" NUMBER NOT NULL ENABLE, 
	"FK_CITYOBJECT_ID" NUMBER,
	 CONSTRAINT "CAFM_FACADE_PK" PRIMARY KEY ("ID"),
	 CONSTRAINT "CAFM_FACADE_CAFM_BUILDING_FK1" FOREIGN KEY ("FK_CAFM_BUILDING")
    REFERENCES "ANDREAS"."CAFM_BUILDING" ("ID") ENABLE, 
	 CONSTRAINT "CAFM_FACADE_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID")
	  REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE
   );

Each building has several facades. and In the "CITYOBJECT" table I have per each entry in either of "CAFM_BUILDIN" AND "CAFM_FACADE" 1 entry. It means If I have 2 building one with 3 and the other with 4 facades in general in the "CITYOBJECT" I have 9 rows(2+(3+4)).

Well. What I have now is a table in it I have "BUILDING_ID", "ID" and all information required for the table "CITYOBJECT". With help of this table I can insert data into the "CITYOBJECT" and the update "FK_CITYOBJECT_ID" in the table "CAFM_BUILDING".

When the table "CAFM_BUILDING" was updated, the column "FK_CIYTOBJECT_ID" in the table "CAFM_FACADE" will be updated. This has been done in the way that for all facades belong to a building, the "FK_CIYTOBJECT_ID" of the facades will be the same as its building's "FK_CIYTOBJECT_ID".

So now the tables I have in this level can be populated with this insert queries:

INSERT INTO CAFM_BUILDING (ID, BUILDING_ID, FK_CITYOBJECT_ID)
VALUES (1, 100.08, 1);
INSERT INTO CAFM_BUILDING (ID, BUILDING_ID, FK_CITYOBJECT_ID)
VALUES (2, 100.10, 2);

INSERT INTO CITYOBJECT (ID, UPDATING_PERSON )
VALUES (1, 'Many');
INSERT INTO CITYOBJECT (ID, UPDATING_PERSON )
VALUES (2, 'Many');

INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (1, '100.08_nord', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (2, '100.08_west', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (3, '100.08_sued', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (4, '100.08_ost', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (5, '100.10_west', 2);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (6, '100.10_sued', 2);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (7, '100.10_ost', 2);

Now what I am thinking of is:
For each entry in the "CAFM_FACADE" take the corresponding row from "CITYOBJECT" (where its "ID" is the same as "FK_CITYBJECT_ID" in the "CAFM_FACADE"), Insert it to the "CITYOBJECT" with a new "ID" and after inserting this row update the corresponding row in the "CAFM_FACADE".

I think there should be a possibility like a for loop or something as it is possible in Java for example and I need to do such a thing with PL/SQL.

Looking forward to see your comments:).

Best regards,
Mandana

Report message to a moderator

Re: Simultaneous update in child table [message #617827 is a reply to message #617822]

Thu, 03 July 2014 15:20

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

If you want to insert the rows in that order, you have to define the constraints as deferrable. I've told you that already. If you don't want to do that, insert into cafm_building first.
What is the problem?
--update: sorry, cityobject first.

[Updated on: Thu, 03 July 2014 15:22]

Report message to a moderator

Re: Simultaneous update in child table [message #617831 is a reply to message #617827]

Thu, 03 July 2014 16:23

many_yammy
Messages: 4
Registered: July 2014

Junior Member

Hummmmm.... I haven't heard anything about deferrable before.I just googled it.But not yet completely got it. Could you explain a bit. How this works? I mean how the constraint will be updated after each insert?

I am not inserting manually. I am working with FME and insert per each row in "CAFM_FACADE" one into "CITYOBJECT".

Report message to a moderator

Re: Simultaneous update in child table [message #617834 is a reply to message #617812]

Thu, 03 July 2014 17:13

many_yammy
Messages: 4
Registered: July 2014

Junior Member

Ok the whole story as below:

I have three tables:
CITYOBJEC

CREATE TABLE "ANDREAS"."CITYOBJECT" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"CREATION_DATE" DATE, 
	"LAST_MODIFICATION_DATE" DATE, 
	"UPDATING_PERSON" VARCHAR2(256 BYTE), 
	"REASON_FOR_UPDATE" VARCHAR2(4000 BYTE),
	 CONSTRAINT "CITYOBJECT_PK" PRIMARY KEY ("ID"));

CAFM_BUILDING

  CREATE TABLE "ANDREAS"."CAFM_BUILDING" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"BUILDING_ID" VARCHAR2(4000 BYTE), 
	"FK_CITYOBJECT_ID" NUMBER, 
	 CONSTRAINT "CAFM_BUILDING_PK" PRIMARY KEY ("ID"),
	 CONSTRAINT "CAFM_BUILDING_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID")
	  REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE);

CAFM_FACADE

CREATE TABLE "ANDREAS"."CAFM_FACADE" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"FACADE_ID" VARCHAR2(4000 BYTE), 
	"FK_CAFM_BUILDING" NUMBER NOT NULL ENABLE, 
	"FK_CITYOBJECT_ID" NUMBER,
	 CONSTRAINT "CAFM_FACADE_PK" PRIMARY KEY ("ID"),
	 CONSTRAINT "CAFM_FACADE_CAFM_BUILDING_FK1" FOREIGN KEY ("FK_CAFM_BUILDING")
    REFERENCES "ANDREAS"."CAFM_BUILDING" ("ID") ENABLE, 
	 CONSTRAINT "CAFM_FACADE_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID")
	  REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE
   );

INSERT INTO CAFM_BUILDING (ID, BUILDING_ID, FK_CITYOBJECT_ID)
VALUES (1, 100.08, 1);
INSERT INTO CAFM_BUILDING (ID, BUILDING_ID, FK_CITYOBJECT_ID)
VALUES (2, 100.10, 2);

INSERT INTO CITYOBJECT (ID, UPDATING_PERSON )
VALUES (1, 'Many');
INSERT INTO CITYOBJECT (ID, UPDATING_PERSON )
VALUES (2, 'Many');

INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (1, '100.08_nord', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (2, '100.08_west', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (3, '100.08_sued', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (4, '100.08_ost', 1);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (5, '100.10_west', 2);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (6, '100.10_sued', 2);
INSERT INTO CAFM_FACADE (ID, FACADE_ID, FK_CAFM_BUILDING)
VALUES (7, '100.10_ost', 2);

Report message to a moderator

Re: Simultaneous update in child table [message #617850 is a reply to message #617831]

Fri, 04 July 2014 01:22

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

many_yammy wrote on Thu, 03 July 2014 22:23

Hummmmm.... I haven't heard anything about deferrable before.I just googled it.But not yet completely got it. Could you explain a bit. How this works? I mean how the constraint will be updated after each insert?

I am not inserting manually. I am working with FME and insert per each row in "CAFM_FACADE" one into "CITYOBJECT".

What did you not understand about deferrable constraints? Have you read the description in the SQL Language Reference?

And please do not duplicate your posts. It annoy people and will discourage assistance.

Report message to a moderator

Previous Topic:	Pass sql file as argument in oracle stored procedure/package
Next Topic:	Generate Graph using PL SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 08:48:44 CDT 2024