更新表的问题：联合两张表如何更新？

[sharetop]

我在mysql中这句话：


update t_records o,t_records n
set n.result=o.result,n.description=o.description,n.owner=o.owner
where o.terminal_version=tv2 and o.result<>n.result
and n.terminal_version=tversion and n.terminal_id=tid and o.terminal_id=n.terminal_id and n.item_id=o.item_id
and (n.refer=o.refer or (n.refer is null and o.refer is null));


想搬到oracle中，就报ORA-00971:缺少SET关键字。。。

看网上有人用join，我也试了一下：

update t_records o inner join t_records n on o.terminal_id=n.terminal_id and o.item_id=n.item_id and (n.refer=o.refer or (n.refer is null and o.refer is null))
set n.result=o.result,n.description=o.description,n.owner=o.owner
where o.terminal_version=tv2 and o.result<>n.result
and n.terminal_version=tversion and n.terminal_id=tid;

也不行，同样的错误？

怎么办，在oracle中怎么更新一张表的记录但是要联合一下呢？

[shiyiwan]

试试这个

merge into t_records n 
using (select * from t_records where terminal_version = tv2) o 
on (n.result            <> o.result 
and n.item_id           = o.item_id 
and nvl(n.refer,1)      = nvl(o.refer,1) 
and n.terminal_id       = o.terminal_id 
and n.terminal_id       = tid 
and n.terminal_version  = tversion) 
when matched then 
update set n.result     = o.result
          ,n.description= o.description
          ,n.owner      = o.owner;

[zuzuou]

这个语句只能在10g以上才能用。
9i的话还必须加上else insert项
[Quote=引用 1 楼 shiyiwan 的回复:]
试试这个

SQL codemergeinto t_records n
using (select*from t_recordswhere terminal_version= tv2) oon (n.result<> o.resultand n.item_id= o.item_idand nvl(n.refer,1)= nvl(o.refer,1)and n.terminal_id= o.terminal_idand n.terminal_id= tidand n.terminal_version= tversion)when matchedthenupdateset n.result= o.result
,n.description= o.description
,n.owner= o.owner;
[/Quote]

[zuzuou]

update t_records n
set (result , description , owner) =
(select result , description ,owner
from t_records o
where o.terminal_version=tv2 and o.result <>n.result
and n.terminal_version=tversion and n.terminal_id=tid and o.terminal_id=n.terminal_id and n.item_id=o.item_id
and (n.refer=o.refer or (n.refer is null and o.refer is null)
)
where exists
(
select 1
from t_records o
where o.terminal_version=tv2 and o.result <>n.result
and n.terminal_version=tversion and n.terminal_id=tid and o.terminal_id=n.terminal_id and n.item_id=o.item_id
and (n.refer=o.refer or (n.refer is null and o.refer is null)
)

[sharetop]

试了一下merge into，搞定，除了有一个错误：不能更新在on中出现的字段，所以把那个o.result<>n.result去掉即可。。。

给分，多谢。

[逆风向前进]

果然好用， 遇到同样的问题。