Home » SQL & PL/SQL » SQL & PL/SQL » updating multiple tables in one sql statement
updating multiple tables in one sql statement [message #191646] Thu, 07 September 2006 07:56 Go to next message
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 #191654 is a reply to message #191646] Thu, 07 September 2006 09:34 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You cant in a single query.
Re: updating multiple tables in one sql statement [message #191655 is a reply to message #191654] Thu, 07 September 2006 09:44 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member


Hi,

My boss wnt to do it in sigle query. He also dont have any clue.

Re: updating multiple tables in one sql statement [message #191657 is a reply to message #191655] Thu, 07 September 2006 10:02 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

As I said, It's not possible in a singel query. No matter what your boss want.

[Updated on: Thu, 07 September 2006 10:02]

Report message to a moderator

Re: updating multiple tables in one sql statement [message #191666 is a reply to message #191657] Thu, 07 September 2006 11:17 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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.
I'll skip INSERT statements and show only what's in there:
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          0
Here's workaround - a trigger on EM_FILTERS table:
SQL> 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.
Now, updating only one table - EM_FILTERS - all three of them will get updated:
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>
Maybe you'll make your boss happy with this solution. Or, on the other hand, you won't.
Re: updating multiple tables in one sql statement [message #191668 is a reply to message #191666] Thu, 07 September 2006 11:28 Go to previous message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

Thanks I done same thing. Its working fine . I am updating only one table and through trigger others.


Thanks again.
Previous Topic: rollover period
Next Topic: Aggregate function
Goto Forum:
  


Current Time: Wed Dec 07 18:53:11 CST 2016

Total time taken to generate the page: 0.23168 seconds