r***e 发帖数: 31 | 1 I have two relations A(a1,a2), B(b1,b2). I want to update the value of b2 to
a2 if a1 and b1 has the same value. How to write a query? |
s******n 发帖数: 45 | 2 update B
set B.b2=A.a2
from A,B
where A.a1=B.b1
【在 r***e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : I have two relations A(a1,a2), B(b1,b2). I want to update the value of b2 to : a2 if a1 and b1 has the same value. How to write a query?
|
r***e 发帖数: 31 | 3 It's not correct, I tried in ORACLE, it does not support update sth from
tables.
Does anyone know the answer?
to
【在 s******n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : update B : set B.b2=A.a2 : from A,B : where A.a1=B.b1
|
s******n 发帖数: 45 | 4 Hehe, it works on sql server.
【在 r***e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : It's not correct, I tried in ORACLE, it does not support update sth from : tables. : Does anyone know the answer? : : to
|
s******n 发帖数: 45 | 5 Try this with Oracle:
update B
set B.b2= (select A.a2 from A where A.a1=B.b1)
在 swingfan (狮子座小马的爸爸) 的大作中提到: 】 |
r***e 发帖数: 31 | 6 Acturally, I tried this before, it only works when you have unique a2 values.
【在 s******n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : Try this with Oracle: : update B : set B.b2= (select A.a2 from A where A.a1=B.b1) : 在 swingfan (狮子座小马的爸爸) 的大作中提到: 】
|
s******n 发帖数: 45 | 7 en. u mean the subquery must return one row only. sigh... the sql server
syntax makes more sense, wonder what oracle is thinking ...
【在 r***e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : Acturally, I tried this before, it only works when you have unique a2 values.
|
k****h 发帖数: 27 | 8 try this explicit cursor in loop:
/
BEGIN
FOR B in (select * from B)
LOOP
BEGIN
UPDATE A
set A.a2=(B.b2)
where A.a1=B.B1
END;
END LOOP;
END;
/
values.
【在 s******n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : en. u mean the subquery must return one row only. sigh... the sql server : syntax makes more sense, wonder what oracle is thinking ...
|