CODEWITHSUNDEEP
oracle-databaseoracle10goracle11g
Murali Krishna Pinjala
Murali Krishna Pinjala

Reputation: 403

how to update multiple tables in oracle DB?

i am using two tables in my oracle 10g. the first table having the keyword,count,id(primary key) and my second table having id, timestamp..

but i am doing any chages in the first table(keyword,count) it will reflect on the my second table timestamp.. i am using id as reference for both the tables...

table1:

CREATE TABLE Searchable_Keywords
(KEYWORD_ID NUMBER(18) PRIMARY KEY,
KEYWORD VARCHAR2(255) NOT NULL,
COUNT  NUMBER(18) NOT NULL,
CONSTRAINT Searchable_Keywords_unique UNIQUE(KEYWORD)
);

table2:

CREATE TABLE Keywords_Tracking_Report
(KEYWORD_ID NUMBER(18),
PROCESS_TIMESTAMP TIMESTAMP(8) 
);

how can update one table with reference of another table..

help me plz...

Upvotes: 0

Views: 4869

Answers (2)

Bharat
Bharat

Reputation: 6866

You can use instead of trigger to do this.

For this you follow the below process

SQL> create or replace view v_for_update
2 as
3 select e.keyword,d.id,e.count
4 from Keywords_Tracking_Report  e, Keywords_Tracking_Report  d
5 where e.id=d.id
6 /

View created.

SQL> create or replace trigger tr_on_v_for_update
2 instead of update on v_for_update
3 begin
4
5 update Keyword_table set Keyword= :new.Keyword, count= :new.count
6 where id=:old.id;
7
8 update Keywords_Tracking_Report set timestamp= :new.timestamp
9 where id=:old.id;
12
13 end;
14 /

Trigger created.

Now with single sql statement you can update multiple tables

 SQL> update v_for_update set keyword='xyz',count = 2, timestamp = sysdate
 where id=1;

Upvotes: 0

BillThor
BillThor

Reputation: 7596

Use an after insert or update trigger on table1 to manage table2.

Upvotes: 1

Related Questions