oracle update两个表的时候，到底怎么用的

[jieguo]

update操作发生在两个表的时候，比如要把K2.d001赋给k1.d001,在三个条件相等的情况下：
Access中可以写
UPDATE Kbgb AS k1, kbgb02 AS k2 SET k1.d001 = k2.d001
WHERE k1.kdlh=k2.kdlh And k1.xyear=k2.xyear And k1.xcode=k2.xcode {支持别名}

sqlserver中需要这样写，
update Kbgb04 set kbgb04.d001=kbgb.d001
from kbgb WHERE (kbgb04.kdlh=kbgb.kdlh) and (kbgb04.xyear=kbgb.xyear) and (kbgb04.xcode=kbgb.xcode)

但在oracle中要怎么写呢：

[ojuju10]

update Kbgb04 set kbgb04.d001=kbgb.d001
from kbgb,Kbgb04 WHERE (kbgb04.kdlh=kbgb.kdlh) and (kbgb04.xyear=kbgb.xyear) and (kbgb04.xcode=kbgb.xcode)

[阿三]

update a set a.col1=(select b.col2 from b where a.col3=b.col3)
where a.col3 in (select b.col3 from b)
这里要加一个限制条件，否则更新表a会把不符合条件的也更新了

[hebo2005]

[Quote=引用 1 楼 ojuju10 的回复:]

update Kbgb04 set kbgb04.d001=kbgb.d001
from kbgb,Kbgb04 WHERE (kbgb04.kdlh=kbgb.kdlh) and (kbgb04.xyear=kbgb.xyear) and (kbgb04.xcode=kbgb.xcode)
[/Quote]
你去试试看行不行再贴上来好吧
oracle 里的update没有from的

[hebo2005]

UPDATE kbgb k1
   SET k1.d001 =
          (SELECT k2.d001
             FROM kbgb02 k2
            WHERE k1.kdlh = k2.kdlh
              AND k1.xyear = k2.xyear
              AND k1.xcode = k2.xcode)
 WHERE EXISTS (
          SELECT 1
            FROM kbgb02 k2
           WHERE k1.kdlh = k2.kdlh
             AND k1.xyear = k2.xyear
             AND k1.xcode = k2.xcode)