Home » SQL & PL/SQL » SQL & PL/SQL » Update multiple rows (Oracle9i)
Update multiple rows [message #435143] Mon, 14 December 2009 11:57 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I am working in a temporary table.


Iinitially the table has data like this



  root_id    parent_id  child_id  col1 col2 col3

   1              20                x    
   2              30                y





Now I have to fetch the value from other table such that it should be updated like this

 
  root_id    parent_id  child_id  col1  col2   col3

   1              20       10         x   1
   1              20       10         x   2 
   2              30       15         y   1 
   2              30       15         y   2 
   2              30       15         y   3 
....
....

Is this possible with a update statement?

Thanks
Re: Update multiple rows [message #435144 is a reply to message #435143] Mon, 14 December 2009 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, No, Maybe, Most likely not, we don't know what is or is not in the "other table".

Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Mon, 14 December 2009 12:01]

Report message to a moderator

Re: Update multiple rows [message #435145 is a reply to message #435143] Mon, 14 December 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>Is this possible with a update statement?
NO, because UPDATE does not add new rows to table
Re: Update multiple rows [message #435146 is a reply to message #435145] Mon, 14 December 2009 12:06 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks !

I wanted to update values from other tables based on matching column col1. My oracle version is 9.2.0.4

[Updated on: Mon, 14 December 2009 12:06]

Report message to a moderator

Re: Update multiple rows [message #435147 is a reply to message #435146] Mon, 14 December 2009 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 14 December 2009 19:00
...
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

Regards
Michel

Re: Update multiple rows [message #435148 is a reply to message #435143] Mon, 14 December 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I am working in a temporary table.
More often than not temporary tables are not needed in Oracle.

Typically they are carry overs from other RDBMS limitations.
Re: Update multiple rows [message #435149 is a reply to message #435148] Mon, 14 December 2009 12:24 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Blackswan.

I am using temp table for multiple updates and later will update the main table after computation

[Updated on: Mon, 14 December 2009 12:25]

Report message to a moderator

Re: Update multiple rows [message #435150 is a reply to message #435149] Mon, 14 December 2009 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I am using temp table for multiple updates.
It is POOR design when you store same data in multiple places.

Design to Third Normal Form to eliminate duplicate data!

[Updated on: Mon, 14 December 2009 12:38]

Report message to a moderator

Re: Update multiple rows [message #435151 is a reply to message #435149] Mon, 14 December 2009 12:43 Go to previous message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I am using temp table for multiple updates and later will update the main table after computation
Nothing prevent underlying data to change between two events above.

With "same" data residing in multiple locations, eventually data gets changed in 1 place & not the other leading to inconsistent data reporting.

You get two different answers depending upon which data source is queried.

You are building an inefficient & unreliable application.
Previous Topic: Easiest way to delete duplicate rows?
Next Topic: PIVOT - sum( case statement) when joining giving wrong results
Goto Forum:
  


Current Time: Tue Sep 27 17:55:23 CDT 2016

Total time taken to generate the page: 0.07851 seconds