sql – 我的更新语句与Oracle中的连接有什么问题？

我正在使用Oracle 10g数据库.

我有以下两个表：

T_DEBTOR :
    - ID_DEBTOR
    - HEADER
T_ELEMENT :
    - ID_ELEMENT
    - ID_DEBTOR
    - INSURER

这两个表使用ID_DEBTOR字段连接.

我想仅在HEADER不为空时才使用关联的T_DEBTOR.HEADER更新T_ELEMENT.INSURER值.
换句话说：

If T_DEBTOR.HEADER != null
    Then T_ELEMENT.INSURER = T_DEBTOR.HEADER
    Else T_ELEMENT.INSURER is not modified!

我试图使用以下SQL查询：

update
    T_ELEMENT elt
    set elt.INSURER = (
        select HEADER
            from T_DEBTOR debtor
            where
                debtor.HEADER is not null
                and debtor.ID_DEBTOR = elt.ID_DEBTOR);

此查询适用于链接到具有HEADER非null的debtors的所有元素.
但是,当T_DEBTOR.HEADER为null时,此查询将T_ELEMENT.INSURER设置为null,这是不正确的.

即：

If T_DEBTOR.HEADER != null
    Then T_ELEMENT.INSURER = T_DEBTOR.HEADER   --> This part is OK
    Else T_ELEMENT.INSURER is set to null      --> This part is NOT OK

我的查询有什么问题？

编辑,关于Brian Storrar答案：

我想做的是这样的：

update
    T_ELEMENT elt
    set elt.INSURER = (
        select HEADER
            from T_DEBTOR debtor
            where
                debtor.HEADER is not null
                and debtor.ID_DEBTOR = elt.ID_DEBTOR)
    where debtor.HEADER is not null;

解决方法

为了模拟您的情况,我创建了示例表：

SQL> create table t_debtor(id_debtor,header)
  2  as
  3  select 1,'Header 1' from dual union all
  4  select 2,null from dual union all
  5  select 3,'Header 3' from dual
  6  /

Tabel is aangemaakt.

SQL> create table t_element (id_element,id_debtor,insurer)
  2  as
  3  select 1,1,'to be updated' from dual union all
  4  select 2,'to be updated' from dual union all
  5  select 3,2,'not to be updated' from dual union all
  6  select 4,'not to be updated' from dual union all
  7  select 5,3,'to be updated' from dual
  8  /

Tabel is aangemaakt.

使用当前的更新语句,问题变得清晰：“不要更新”值设置为NULL：

SQL> update
  2      T_ELEMENT elt
  3      set elt.INSURER = (
  4          select HEADER
  5              from T_DEBTOR debtor
  6              where
  7                  debtor.HEADER is not null
  8                  and debtor.ID_DEBTOR = elt.ID_DEBTOR)
  9  /

5 rijen zijn bijgewerkt.

SQL> select * from t_element
  2  /

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- -----------------
         1          1 Header 1
         2          1 Header 1
         3          2
         4          2
         5          3 Header 3

5 rijen zijn geselecteerd.

执行此更新的最佳方法是更新两个表的连接.但是有一些限制：

SQL> rollback
  2  /

Rollback is voltooid.

SQL> update ( select elt.insurer
  2,dtr.header
  3             from t_element elt
  4,t_debtor dtr
  5            where elt.id_debtor = dtr.id_debtor
  6              and dtr.header is not null
  7         )
  8     set insurer = header
  9  /
   set insurer = header
       *
FOUT in regel 8:
.ORA-01779: cannot modify a column which maps to a non key-preserved table

通过旁路ujvc提示,我们可以绕过这个限制.
但除非你确实知道t_debtor.id_debtor是唯一的,否则不建议这样做.

SQL> update /*+ bypass_ujvc */
  2         ( select elt.insurer
  3,dtr.header
  4             from t_element elt
  5,t_debtor dtr
  6            where elt.id_debtor = dtr.id_debtor
  7              and dtr.header is not null
  8         )
  9     set insurer = header
 10  /

3 rijen zijn bijgewerkt.

SQL> select * from t_element
  2  /

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- -----------------
         1          1 Header 1
         2          1 Header 1
         3          2 not to be updated
         4          2 not to be updated
         5          3 Header 3

5 rijen zijn geselecteerd.

最好只添加一个主键.你可能已经有了这个：

SQL> rollback
  2  /

Rollback is voltooid.

SQL> alter table t_debtor add primary key (id_debtor)
  2  /

Tabel is gewijzigd.

SQL> update ( select elt.insurer
  2,t_debtor dtr
  5            where elt.id_debtor = dtr.id_debtor
  6              and dtr.header is not null
  7         )
  8     set insurer = header
  9  /

3 rijen zijn bijgewerkt.

SQL> select * from t_element
  2  /

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- -----------------
         1          1 Header 1
         2          1 Header 1
         3          2 not to be updated
         4          2 not to be updated
         5          3 Header 3

5 rijen zijn geselecteerd.

问候,抢.

（编辑：淮安站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!