OraFAQ Forum: SQL & PL/SQL » Multiple Updates in table with Single Update Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Updates in table with Single Update Statement

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

Multiple Updates in table with Single Update Statement [message #132589]

Mon, 15 August 2005 00:34

mymot
Messages: 225
Registered: July 2005

Senior Member

HI,

Is there any i could update single table with mutiple columns?

UPDATE table
SET column = expression
WHERE predicates

select * from tt;

ID NAME DEPT
---------- ---------- --------------------
1 sam s
2 ss p

update tt set name = 'aa' , ss = 'pp' where id = 1;

i cant set 2 columns in a single update????

Thanks

Report message to a moderator

Re: Multiple Updates in table with Single Update Statement [message #132590 is a reply to message #132589]

Mon, 15 August 2005 01:23

amrita418
Messages: 16
Registered: July 2005
Location: San Diego

Junior Member

Your update statement in itself looks good, but I think you do not have a column ss to update (alteast from your sample data).

Try something like:

update    tt 
set       name = 'aa', 
          Dept = 'pp' 
where     Id = 1

Regards,
AA

Report message to a moderator

Re: Multiple Updates in table with Single Update Statement [message #132600 is a reply to message #132590]

Mon, 15 August 2005 02:14

mymot
Messages: 225
Registered: July 2005

Senior Member

Thanks AA,

Actually i should have asked the question bit differently

have got 3 tables

tab1(id,status,time);
tab2(id,name,dept);
tab3(id,time,type);

now for single id , i want to update all the tables in one Update statement

update all
set status = 'clo'
set name = 'ss',
set time = 'sysdate -1',
set type = 'local'
where id = 1;

regds

[Updated on: Mon, 15 August 2005 07:00]

Report message to a moderator

Re: Multiple Updates in table with Single Update Statement [message #133138 is a reply to message #132589]

Wed, 17 August 2005 16:14

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

It's not possible to do *directly* do this, since Oracle will always have the restriction imposed on it of ORA-01776, i.e.

SQL> UPDATE ( SELECT t1.status, t2.name, t1.time, t3.type, t1.id
  2             FROM 
  3               tab1 t1
  4               INNER JOIN tab2 t2 ON ( t2.id = t1.id )
  5               INNER JOIN tab3 t3 ON ( t3.id = t1.id )
  6         )
  7     SET status = 'CLO',
  8         name   = 'SS',
  9         time   = 'SYSDATE-1',
 10         type   = 'LOCAL'
 11   WHERE id = 1;
       name   = 'SS',
       *
ERROR at line 8:
ORA-01776: cannot modify more than one base table through a join view

So, any solution that you come up with will (at the end of the day) result in multiple different UPDATE statements, one for each table.

However, if you did want to make it *look* like you're doing it in one update statement, then one approach (of which there will be multiple others, of course) would be to use an INSTEAD OF trigger, i.e.

SQL> CREATE TABLE tab1 ( id  NUMBER(10) PRIMARY KEY,
  2                      status  VARCHAR2(3),
  3                      time    VARCHAR2(50) );

Table created.

SQL> CREATE TABLE tab2 ( id  NUMBER(10) PRIMARY KEY,
  2                      name  VARCHAR2(2),
  3                      dept  VARCHAR2(20) );

Table created.

SQL> CREATE TABLE tab3 ( id  NUMBER(10) PRIMARY KEY,
  2                      time  VARCHAR2(50),
  3                      type  VARCHAR2(50) );

Table created.

SQL> CREATE OR REPLACE VIEW update_view
  2  AS
  3  SELECT t1.status, t2.name, t1.time, t3.type, t1.id
  4    FROM 
  5      tab1 t1
  6      INNER JOIN tab2 t2 ON ( t2.id = t1.id )
  7      INNER JOIN tab3 t3 ON ( t3.id = t1.id )
  8  /

View created.

SQL> CREATE OR REPLACE TRIGGER update_instead_trig
  2  INSTEAD OF UPDATE ON update_view
  3  FOR EACH ROW
  4  BEGIN
  5    UPDATE tab1 SET status = :new.status,
  6                    time   = :new.time
  7     WHERE id = :new.id;
  8    
  9    UPDATE tab2 SET name = :new.name
 10     WHERE id = :new.id;
 11  
 12    UPDATE tab3 SET type = :new.type
 13     WHERE id = :new.id;
 14  END update_instead_trig;
 15  /

Trigger created.

SQL> INSERT INTO tab1 VALUES ( 1, 'OPN', 'SYSDATE+1' );

1 row created.

SQL> INSERT INTO tab2 VALUES ( 1, 'TT', 'DEV' );

1 row created.

SQL> INSERT INTO tab3 VALUES ( 1, 'SYSDATE+1', 'EXTERNAL' );

1 row created.

SQL> UPDATE update_view
  2     SET status = 'CLO',
  3        name   = 'SS',
  4        time   = 'SYSDATE-1',
  5        type   = 'LOCAL'
  6  WHERE id = 1;

1 row updated.

SQL> SELECT * FROM tab1;

        ID STA TIME
---------- --- --------------------------------------------------
         1 CLO SYSDATE-1

SQL> SELECT * FROM tab2;

        ID NA DEPT
---------- -- --------------------
         1 SS DEV

SQL> SELECT * FROM tab3;

        ID TIME        TYPE
---------- ----------- -------------------------------------------------
         1 SYSDATE+1   LOCAL

But, it still boils down to 3 update statements.

Rgds

Report message to a moderator

Previous Topic:	dynamic datatype sizing - possible?
Next Topic:	Need help with program performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:31:01 CDT 2024