| updating multiple tables in one sql statement [message #191646] |
Thu, 07 September 2006 07:56  |
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
|
Member |
|
|
Hi,
I have Three tables:
em_ipauth_sites( site_id number pk,
domain_name varchar2(50),
deleted number(1) default 0)
em_ipauth_companies( company_id pk number,
company_name varchar2(50),
deleted number(1) default 0 )
em_ipauth_filters( filter_id pk number,
filter varchar2(50),
site_id number fk,
deleted number(1) default 0,
company_id number fk )
can any one suggest me a single query to update "deleted" column of all three tables ( " I dont wnt to write three update statment")
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: updating multiple tables in one sql statement [message #191666 is a reply to message #191657] |
Thu, 07 September 2006 11:17  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
There might be a workaround this problem; not exactly one update statement for three tables, but (perhaps) close enough.
It would, of course, help if you provided actual CREATE TABLE statements along with INSERT sample data ones.
First, create those tables:SQL> create table em_sites
2 (site_id number primary key,
3 domain_name varchar2(50),
4 deleted number(1) default 0);
Table created.
SQL> create table em_companies
2 (company_id number primary key,
3 company_name varchar2(50),
4 deleted number(1) default 0);
Table created.
SQL> create table em_filters
2 (filter_id number primary key,
3 filter varchar2(50),
4 site_id number(1) references em_sites (site_id),
5 company_id number(1) references em_companies (company_id),
6 deleted number(1) default 0);
Table created. SQL> select * From em_sites;
SITE_ID DOMAIN_NAME DELETED
---------- -------------------------------------------------- ----------
1 Zagreb 0
2 Dubrovnik 0
SQL> select * From em_companies;
COMPANY_ID COMPANY_NAME DELETED
---------- -------------------------------------------------- ----------
1 INA 0
2 Pliva 0
3 HEP 0
SQL> select * From em_filters;
FILTER_ID FILTER SITE_ID COMPANY_ID DELETED
---------- ---------- ---------- ---------- ----------
300 fil 1 1 1 0
301 fil 2 1 2 0SQL> create or replace trigger trg_filt
2 after update of deleted on em_filters
3 for each row
4 begin
5 update em_sites s
6 set s.deleted = :new.deleted
7 where s.site_id = :new.site_id;
8
9 update em_companies c
10 set c.deleted = :new.deleted
11 where c.company_id = :new.company_id;
12 end;
13 /
Trigger created. SQL> update em_filters set deleted = 1
2 where filter_id = 300;
1 row updated.
SQL> select * From em_sites;
SITE_ID DOMAIN_NAME DELETED
---------- -------------------------------------------------- ----------
1 Zagreb 1
2 Dubrovnik 0
SQL> select * From em_companies;
COMPANY_ID COMPANY_NAME DELETED
---------- -------------------------------------------------- ----------
1 INA 1
2 Pliva 0
3 HEP 0
SQL> select * From em_Filters;
FILTER_ID FILTER SITE_ID COMPANY_ID DELETED
---------- ---------- ---------- ---------- ----------
300 fil 1 1 1 1
301 fil 2 1 2 0
SQL> |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
