首先mssql和oracle两边都支持update set where
update xxx set col = 'xxx' where col = 'sss';
但是SQL SERVER也支持update set from
update xxx set xxx.col = yyy.col from yyy where ......;
ORACLE这边不支持,于是研究了一下有了一种可以代替的sql:
update xxx set xxx.col = (select yyy.col from yyy where ......);
实践之后发现会把xxx.col全变成了yyy.col,于是又有了新的
update xxx set xxx.col = (select yyy.col from yyy where ......)where exists (select 1 yyy.col from yyy where ....);
但是又有一种情况就是xxx.col 有一对多的情况,于是
update xxx set xxx.col = (select yyy.col from yyy where ...... and rownum <= 1)where exists (select 1 yyy.col from yyy where ....);