如何使用公共子选择处理Oracle更新中返回的行

Consider the update:

考虑更新:

UPDATE table1
SET c1 = NVL(( SELECT d1 FROM table2 WHERE table1.id = table2.id ), 0),
c2 = NVL(( SELECT d2 FROM table2 WHERE table1.id = table2.id ), 0)

The NVL function handles the case where the sub-select returns no rows.

NVL函数处理子选择不返回任何行的情况。

Is there a good way to rewrite this (without repeating the sub-select) using this type of syntax:

有没有一种很好的方法可以使用这种类型的语法重写它(不重复子选择):

UPDATE table1 SET (c1,c2) = ( SELECT d1, d2 FROM table2 where table1.id = table2.id )

such that the case where the sub-select returns now rows is handled.

这样就可以处理子选择现在返回行的情况。

1 个解决方案

#1

I would change the subselect to include a left outer join on t1, and then nvl the results in that case, eg:

我会更改子选择以在t1上包含左外连接,然后在这种情况下nvl结果,例如:

drop table t1;
drop table t2;

create table t1 (col1 number, col2 number, col3 number);

create table t2 (col1 number, col2 number, col3 number);

insert into t1 values (1, 10, 100);
insert into t1 values (2, 20, 200);

insert into t2 values (1, 100, 1000);

commit;

update t1
set (col2, col3) = (select nvl(col2, 0), nvl(col3, 0)
                    from   (select a.col1, b.col2, b.col3
                            from   t1 a
                                   left outer join t2 b on (a.col1 = b.col1)) c
                    where  c.col1 = t1.col1);

commit;

select * from t1;

      COL1       COL2       COL3
---------- ---------- ----------
         1        100       1000
         2          0          0