我在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中怎么更新一张表的记录但是要联合一下呢?
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中怎么更新一张表的记录但是要联合一下呢?
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;
9i的话还必须加上else insert项
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)
)