关于oracle中的一个update SQL

Question

我想update wh5sku.stdcube 的数据,如果AND wh5sku.stdcube < wh6sku.stdcube 就把 wh5的更新为wh6的值(也就是取大值)
用这个语句可以搜索出来由900跳
select wh5sku.stdcube,wh6sku.stdcube
from wh5.sku wh5sku, wh6.sku wh6sku
WHERE wh5sku.sku = wh6sku.sku
AND wh5sku.storerkey = wh6sku.storerkey
AND wh6sku.stdcube > wh5sku.stdcube

可是oracle中update不能用from
我试过用exists 和in 可能写法有问题,未成功
失败的例子:
UPDATE wh5.sku
SET stdcube = (select wh6sku.stdcube
from wh6.sku wh6sku,wh5.sku wh5sku
where wh6sku.sku = wh5sku.sku
AND wh6sku.storerkey = wh5sku.storerkey)
--select stdcube from wh5.sku
where exists (select *
from wh5.sku wh5sku, wh6.sku wh6sku
WHERE wh5sku.sku = wh6sku.sku
AND wh5sku.storerkey = wh6sku.storerkey
AND wh6sku.stdcube > wh5sku.stdcube)

怎样写才能实现这个简单的update呢?
tj_angela,我运行后错误提示:ORA-00904:"B"."stdcube":无效的标识符
badkano 的提示:ORA-01427: 单行子查询返回多于一个行
杀·破狼的提示:ORA-00933: SQL 命令未正确结束,指向了left那个字,因为orcale不是这样用外部连接的.
我还在琢磨~~
正确的全部是:
UPDATE wh5.sku a
set a.stdcube=(select b.stdcube from wh6.sku b
where a.sku = b.sku
and a.storerkey=b.storerkey )

where exists
(select *
from wh6.sku b
where b.sku = a.sku
AND b.storerkey = a.storerkey
and a.stdcube < b.stdcube)

Answer 1

update wh5sku a set a.stdcube=(select b.stdcube from wh6sku b
where a.storerkey=b.storerkey
and a.stdcube>b.stdcube)
-----------补充-----------
报那个错是因为，返回值多于一个，也就是a.storerkey=b.storerkey
and a.stdcube>b.stdcube查出来的东西不对劲，仔细检查你的数据或者你把你表打个包发一下

Answer 2

UPDATE wh5.sku a
SET a.stdcube = b.stdcube
from wh6.sku b
where exists
(select *
from wh6.sku b
where b.sku = a.sku
AND b.storerkey = a.storerkey
and a.stdcube < b.stdcube)

取别名是为了看起来方便，你这个语句这么乱怎么看

Answer 3

很简单嘛:
UPDATE wh5.sku a
SET a.stdcube =b.stdcube from wh6.sku b
where b.sku =a.sku
AND b.storerkey =a.storerkey AND and a.stdcube < b.stdcube

--补充--------------
oracle中update不能用from? 那试试用连接看看吧
UPDATE wh5.sku a
SET a.stdcube =b.stdcube left join wh6.sku b
on b.sku =a.sku
AND b.storerkey =a.storerkey AND and a.stdcube < b.stdcube