| how to write a correct sql sentence. [message #374019] |
Mon, 21 May 2001 03:18  |
mhy
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
dear sir;
I have met a problem: I have two tables name aa and bb,their struct are similar.fg.aa (aa1 number,aa2 varchar2(10)) and bb(bb1 number,bb2 varchar2(10)),both of them have 100 rows data,and most aa.aa1 equal to bb.bb1 in value .
how can I update aa set aa.aa2 =bb.bb2 in value where aa.aa1=bb.bb1.I know one way is to create a middle table,if I won't to create middle table ,how can I do?
|
|
|
|
| Re: how to write a correct sql sentence. [message #374031 is a reply to message #374019] |
Mon, 21 May 2001 17:46  |
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
|
Junior Member |
|
|
assuming aa.aa1 and bb.bb1 are primary keys of their respective tables then
update aa set aa.aa2=(select bb.bb2 from bb where aa.aa1=bb.bb1);
if they are prinmay keys and you want set max of bb2 for given aa1 then
(Please note : if the bb table is big then have composite index on bb1 and bb2)
update aa.aa2=( select max(bb.bb2) from bb where aa.aa1=bb.bb1);
if they are not primary keys and you want first value only for given aa1 then
(Please note : if the bb table is big then have composite index on bb1 and bb2)
update aa set aa.aa2=(select bb.bb2 from bb where aa.aa1=bb.bb1 and rownum=1);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
