怎么用Update实现这个? - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

怎么用Update实现这个?

怎么用Update实现这个?# Database - 数据库

c*t2008-01-22 08:01

1 楼

in Oracle, Table A has the following records:
F1, F2, F3
a1 x v1
a1 y v2
a2 x v1'
a2 y v2'
How to write update statement, and make the records look like:
a1, x, v1-v2
a1, y, v2
a2, x, v1'-v2'
a2, y, v2'

c*t2008-01-22 08:01

2 楼

update (select x.F1, x.F2, x.F2-y.F2 as F2'
from A x, A y
where x.F1=y.F1 and x.F2='x' and y.F2='y'
)
set F2=F2'
ORA-01779: cannot modify a column which maps to a non key-preserved table

【在 c**t 的大作中提到】

: in Oracle, Table A has the following records:
: F1, F2, F3
: a1 x v1
: a1 y v2
: a2 x v1'
: a2 y v2'
: How to write update statement, and make the records look like:
: a1, x, v1-v2
: a1, y, v2
: a2, x, v1'-v2'

c*t2008-01-22 08:01

3 楼

update A x
set F3 = F3 - (select y.F3 from A y where x.F1=y.F1 and y.F2='y')
where x.F2='x'
ORA-01407: cannot update ... to NULL

【在 c**t 的大作中提到】

B*g2008-01-22 08:01

4 楼

NND, 写错了
update A x
set F3 = F3 - NVL((select y.F3 from A y where x.F1=y.F1 and y.F2='y' AND
ROWNUM = 1)， 0）
where x.F2='x'

there
AND

c*t2008-01-22 08:01

5 楼

your solution is bettern than mine:
merge into A orig using
(select F1, F2, SUM(F3)
from (select F1, F2, F3 from A where F2='x'
union
select F1, F2, -1*F3 as F3 from A where F2='y')
group by F1, F2
) temp
on orig.F1=temp.F1
WHEN MATCHED THEN UPDATE
set F3=temp.F3;

【在 B*****g 的大作中提到】

: NND, 写错了
: update A x
: set F3 = F3 - NVL((select y.F3 from A y where x.F1=y.F1 and y.F2='y' AND
: ROWNUM = 1)， 0）
: where x.F2='x'
:
: there
: AND