Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect updates using joins
bulk collect updates using joins [message #252172] Wed, 18 July 2007 01:17 Go to next message
raj_23
Messages: 7
Registered: July 2007
Junior Member
I have 2 tables

create table TABLE_FILE
(
  HICN VARCHAR2(12) NOT NULL,
  FIELD1 NUMBER(10) NOT NULL DEFAULT 0,
  FIELD2 NUMBER(10) NOT NULL DEFAULT 0,
  FIELD3 NUMBER(10) NOT NULL DEFAULT 0
)


Data:
HICN     FIELD1     FIELD2     FIELD3
1
2
3
4


CREATE TABLE TABLE_DATA 
(
  HICN VARCHAR2(12) NOT NULL,
  FIELD1 NUMBER(10) NOT NULL ,
  FIELD2 NUMBER(10) NOT NULL ,
  FIELD3 NUMBER(10) NOT NULL ,
  FIELD4 NUMBER(10) NOT NULL 
)


Data:
HICN     FIELD1     FIELD2     FIELD3
1         200         250       1 
2         300         350       1     
3         400         240       1 
4         500         550       1



I need to join the two tables by the key (HICN) and update the
values from TABLE_DATA to TABLE_FILE....

Since oracle doesn't allow updates using joins,how do we do this ?

Thank you
raj
Re: bulk collect updates using joins [message #252182 is a reply to message #252172] Wed, 18 July 2007 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course it does and always did.
It fully follows ANSI rules for this.

Just "update ... set (...) = (select ... from ... where ...)"

Regards
Michel

[Updated on: Wed, 18 July 2007 01:42]

Report message to a moderator

Re: bulk collect updates using joins [message #252220 is a reply to message #252182] Wed, 18 July 2007 03:02 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It also allows Updateable Join Views, which are more efficient over larger volumes.

Ross Leishman
Previous Topic: Concating column data as row
Next Topic: deleting records in a table based on a collection
Goto Forum:
  


Current Time: Fri Dec 09 23:29:28 CST 2016

Total time taken to generate the page: 0.11164 seconds