Home » SQL & PL/SQL » SQL & PL/SQL » update one table from another table
update one table from another table [message #195574] Fri, 29 September 2006 09:02 Go to next message
sridharbadhe
Messages: 1
Registered: September 2006
Junior Member
Hi,
I am having a scenario where I need to update one table from another table. Its not a single column but a set of columns (for example 5 columns).

Think we are having 2 tables, Table1 and Table2.

Table1 is having columns a,b,c,d,e,f and a being the primary key.
Table2 is having columns p,q,r,s,t,u and p being the primary key.

I want to update the columns in Table1 c,d,e,f, from coloumns in Table2 r,s,t,u
where table1.a=table2.p

Please let me know how we can do this.

Thanks In Advance
Sree
Re: update one table from another table [message #195611 is a reply to message #195574] Fri, 29 September 2006 12:33 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Two approaches, one using a standard correlated query, and the other using an updateable view (your primary keys meet the requirement to use this approach):

update t1
   set (c, d, e, f) = (select t2.r, t2.s, t2.t, t2.u
                         from t2
                        where t2.p = t1.a)
 where exists (select null
                 from t2
                where t2.p = t1.a);
                
update (select t1.c, t1.d, t1.e, t1.f,
               t2.r, t2.s, t2.t, t2.u
          from t1,
               t2
         where t2.p = t1.a)
   set c = r,
       d = s,
       e = t,
       f = u;
Re: update one table from another table [message #195612 is a reply to message #195574] Fri, 29 September 2006 12:33 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
update t1 A set (b,c,d,e,f)=
   (select q,r,s,t,u
    from   t2 B
    where  B.p=A.a)
and A.a in (select p from t2)
/


Heh, intersting how Todd and I both used t1 and t2 instead of the generic Table1 and Table2 as the OP stated. I also seem to have updated one extra column, but I'll keep that in there.

[Updated on: Fri, 29 September 2006 15:38]

Report message to a moderator

Previous Topic: Creating table/index scripts fails on one server and works on one
Next Topic: Case in a where clause
Goto Forum:
  


Current Time: Wed Dec 07 14:44:26 CST 2016

Total time taken to generate the page: 0.10858 seconds