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 的大作中提到】
: 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 的大作中提到】
: 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 的大作中提到】
: 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 的大作中提到】
: 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 的大作中提到】
: 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 的大作中提到】
: en. u mean the subquery must return one row only. sigh... the sql server
: syntax makes more sense, wonder what oracle is thinking ...
|
