how to make this query - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

how to make this query

how to make this query# Database - 数据库

r*e2005-03-31 08:03

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*n2005-03-31 08:03

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*e2005-03-31 08:03

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*n2005-03-31 08:03

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*n2005-03-31 08:03

5 楼

Try this with Oracle:
update B
set B.b2= (select A.a2 from A where A.a1=B.b1)
在 swingfan (狮子座小马的爸爸) 的大作中提到: 】

r*e2005-03-31 08:03

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*n2005-03-31 08:03

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*h2005-03-31 08:03

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 ...