我的 Oracle 联接更新语句有什么问题？

发布于 2024-07-24 04:39:03 字数 1397 浏览 6 评论 0原文

我正在使用 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 不为空的债务人的所有元素。但是，当 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;

原文

I am working with an Oracle 10g Database.

I have the following two tables:

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

These two tables are joined using the ID_DEBTOR field.

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!

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);

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.

ie:

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?

Edit, regarding the Brian Storrar answer:

What I want to do is something like that:

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浸婚纱 2024-07-31 04:39:03

好问题。

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

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.

通过您当前的更新语句，问题变得清晰：“不更新”值设置为 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                , 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.

问候，
抢。

Good question.

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.

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

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.

Regards,
Rob.

回复收藏 0 原文

爱情眠于流年 2024-07-31 04:39:03

我找到了解决我的问题的解决方案（添加了where子句）：

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 exists (
        select null
            from T_DEBTOR debtor
            where debtor.HEADER is not null
                and debtor.ID_DEBTOR = elt.ID_DEBTOR);

如果您有更好的解决方案，请随时发布！

I've found a solution to solve my problem (the where clause is added):

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 exists (
        select null
            from T_DEBTOR debtor
            where debtor.HEADER is not null
                and debtor.ID_DEBTOR = elt.ID_DEBTOR);

If you have a better solution, do not hesitate to post it!

回复收藏 0 原文

执着的年纪 2024-07-31 04:39:03

从 Oracle 8i 开始（我没有尝试过使用之前的版本），如果表是“键保留的”（即：如果您要从父子关系中更新子项），则可以更新联接。在这里，如果 id_debtor 是 T_DEBTOR 的主键，您可以：

UPDATE (SELECT e.insurer, d.header
          FROM t_element e, t_debtor d
         WHERE e.id_debtor = d.id_debtor
           AND d.header IS NOT NULL)
   SET insurer = HEADER;

干杯，

--
文森特

since Oracle 8i (I haven't tried with the preceeding versions), you can update a join if the tables are "key-preserved" (i-e: if you're updating the child from in a parent-child relationship). Here, if id_debtor is the primary key of T_DEBTOR, you can :

UPDATE (SELECT e.insurer, d.header
          FROM t_element e, t_debtor d
         WHERE e.id_debtor = d.id_debtor
           AND d.header IS NOT NULL)
   SET insurer = HEADER;

Cheers,

--

Vincent

回复收藏 0 原文

爺獨霸怡葒院 2024-07-31 04:39:03

你尝试过

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

或者类似的东西吗
承认这有点没有选择性，但我认为它会达到你的目的。

Have you tried

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

or something similar
admittedy this is a bit unselective but I think it will do what you intend.

回复收藏 0 原文

反目相谮 2024-07-31 04:39:03

您可以通过更新选择的结果来做到这一点，但表必须“保留键”：

SQL> create table t_debtor ( id_debtor integer, header varchar2(10));

Table created.

SQL> create table t_element (id_element integer, id_debtor integer, insurer varchar2(10));

Table created.

SQL> insert into t_debtor values (1, 'something');

1 row created.

SQL> insert into t_debtor values (2, 'else');

1 row created.

SQL> insert into t_debtor values (3, null);

1 row created.

SQL>
SQL> insert into t_element values (1, 1, 'foo');

1 row created.

SQL> insert into t_element values (2, 2, null);

1 row created.

SQL> insert into t_element values (3, 3, 'bar');

1 row created.

SQL> commit;

Commit complete.

这会创建您的表（提示 - 如果您可以为示例发布 SQL，这将非常有用！）。

现在您可以更新选择的结果以提供您想要的结果...

SQL> update (select e.id_element, d.header header, e.insurer insurer
        from t_debtor d, t_element e
  2          where d.id_debtor = e.id_debtor  3
  4          and d.header is not null)
  5  set insurer = header;
set insurer = header
    *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

这会失败，因为表未保留键，但一些约束将解决此问题：

alter table t_element add constraint t_element_pk primary key (id_element) using index;

alter table t_debtor add constraint t_debtor_pk primary key (id_debtor) using index;

alter table t_element add constraint t_element_debtor_fk foreign key (id_debtor) references t_debtor(id_debtor);

现在更新将起作用，因为表已保留键：

SQL> update (select e.id_element, d.header header, e.insurer insurer
        from t_debtor d, t_element e
        where d.id_debtor = e.id_debtor
        and d.header is not null)
set insurer = header  2    3    4    5  ;

2 rows updated.

SQL> select * from t_element;

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- ----------
         1          1 something
         2          2 else
         3          3 bar

You can do this by updating the results of a select, but the tables have to be 'key preserved':

SQL> create table t_debtor ( id_debtor integer, header varchar2(10));

Table created.

SQL> create table t_element (id_element integer, id_debtor integer, insurer varchar2(10));

Table created.

SQL> insert into t_debtor values (1, 'something');

1 row created.

SQL> insert into t_debtor values (2, 'else');

1 row created.

SQL> insert into t_debtor values (3, null);

1 row created.

SQL>
SQL> insert into t_element values (1, 1, 'foo');

1 row created.

SQL> insert into t_element values (2, 2, null);

1 row created.

SQL> insert into t_element values (3, 3, 'bar');

1 row created.

SQL> commit;

Commit complete.

That creates your tables (hint - it's very useful if you can post SQL for your example!).

Now you can update the results of a select to give what you want ...

SQL> update (select e.id_element, d.header header, e.insurer insurer
        from t_debtor d, t_element e
  2          where d.id_debtor = e.id_debtor  3
  4          and d.header is not null)
  5  set insurer = header;
set insurer = header
    *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

This fails because the table is not key preserved, but a few constraints will solve this:

alter table t_element add constraint t_element_pk primary key (id_element) using index;

alter table t_debtor add constraint t_debtor_pk primary key (id_debtor) using index;

alter table t_element add constraint t_element_debtor_fk foreign key (id_debtor) references t_debtor(id_debtor);

Now the update will work, because the tables are key preserved:

SQL> update (select e.id_element, d.header header, e.insurer insurer
        from t_debtor d, t_element e
        where d.id_debtor = e.id_debtor
        and d.header is not null)
set insurer = header  2    3    4    5  ;

2 rows updated.

SQL> select * from t_element;

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- ----------
         1          1 something
         2          2 else
         3          3 bar

回复收藏 0 原文

檐上三寸雪 2024-07-31 04:39:03

你有没有尝试过

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

Have you tried

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

回复收藏 0 原文