17,089
社区成员
发帖
与我相关
我的任务
分享
select * from t for update
CREATE TABLE t
(
nm char(1),
gd number(4,0),
cnt number(4,0),
mark number(4,0),--唯一标识
sale number(4,0) --卖出数量
)
但是执行
update t set sale= (select
case
when scnt > 100 then
cnt - (scnt - 100)
else
cnt
end cnt
from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,mark from t) a
where cnt - (scnt - 100) > 0
and a.mark=t.mark)
where exists (select 1 from a where mark=t.mark)
报错。
如果去掉最后的where exists (select 1 from a where mark=t.mark则也把用户D也更新
[/quote]
merge into t a
using (select case
when scnt > 100 then
cnt - (scnt - 100)
else
cnt
end cnt, rid
from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,
rowid rid
from t) a
where cnt - (scnt - 100) > 0) b
on (a.rowid = b.rid)
when matched then
update set a.sale = b.cnt
[/quote]
请问知道为啥我的这句 where exists (select 1 from a where mark=t.mark) 会报表不存在吗?错在哪里
select * from t for update
CREATE TABLE t
(
nm char(1),
gd number(4,0),
cnt number(4,0),
mark number(4,0),--唯一标识
sale number(4,0) --卖出数量
)
但是执行
update t set sale= (select
case
when scnt > 100 then
cnt - (scnt - 100)
else
cnt
end cnt
from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,mark from t) a
where cnt - (scnt - 100) > 0
and a.mark=t.mark)
where exists (select 1 from a where mark=t.mark)
报错。
如果去掉最后的where exists (select 1 from a where mark=t.mark则也把用户D也更新
[/quote]
merge into t a
using (select case
when scnt > 100 then
cnt - (scnt - 100)
else
cnt
end cnt, rid
from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,
rowid rid
from t) a
where cnt - (scnt - 100) > 0) b
on (a.rowid = b.rid)
when matched then
update set a.sale = b.cnt
select * from t for update
CREATE TABLE t
(
nm char(1),
gd number(4,0),
cnt number(4,0),
mark number(4,0),--唯一标识
sale number(4,0) --卖出数量
)
但是执行
update t set sale= (select
case
when scnt > 100 then
cnt - (scnt - 100)
else
cnt
end cnt
from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,mark from t) a
where cnt - (scnt - 100) > 0
and a.mark=t.mark)
where exists (select 1 from a where mark=t.mark)
报错。
如果去掉最后的where exists (select 1 from a where mark=t.mark则也把用户D也更新