我在Oracle中加入联接的更新语句有什么问题? [英] What is wrong with my update statement with a join in Oracle?

问题描述

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

I am working with an Oracle 10g Database.

我有以下两个表:

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

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

These two tables are joined using the ID_DEBTOR field.

仅当HEADER不为null时，我才想使用关联的T_DEBTOR.HEADER更新T_ELEMENT.INSURER值. 换句话说:

I want to update the T_ELEMENT.INSURER value with the associated T_DEBTOR.HEADER only if HEADER is not null. In others words:

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

我尝试使用以下SQL查询:

I tried to use the following SQL query:

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的债务人链接的所有元素. 但是，当T_DEBTOR.HEADER为null时，此查询会将T_ELEMENT.INSURER设置为null，这是不正确的.

This query is working for all elements linked to debtors that has a HEADER not null. However, when the T_DEBTOR.HEADER is null, then this query set the T_ELEMENT.INSURER to null, which is not correct.

即:

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

我的查询出了什么问题?

What is wrong with my query?

编辑，关于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;

推荐答案

好问题.

为模拟您的情况，我创建了示例表:

To simulate your situation, I've created sample tables:

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, 1, 'to be updated' from dual union all
  5  select 3, 2, 'not to be updated' from dual union all
  6  select 4, 2, 'not to be updated' from dual union all
  7  select 5, 3, 'to be updated' from dual
  8  /

Tabel is aangemaakt.

使用您当前的update语句，问题变得很明显:不更新"值设置为NULL:

And with your current update statement, the problem becomes clear: the "not to be updated" values are set to 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.

进行此更新的最佳方法是更新两个表的联接.但是有一些限制:

The best way to do this update, is to update a join of both tables. There are some restrictions however:

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是唯一的.

With the bypass ujvc hint, we can circumvent this restriction. But it is not advisable to do so unless you know really really sure that t_debtor.id_debtor is unique.

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.

最好只添加一个主键.您可能已经准备好了这个

It's better to just add a primary key. You'll probably have this one already in place:

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                , 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  /

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.

关于， 罗布.

这篇关于我在Oracle中加入联接的更新语句有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！