我在 Oracle 中加入的更新语句有什么问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/975315/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is wrong with my update statement with a join in Oracle?
提问by Romain Linsolas
I am working with an Oracle10g Database.
我正在使用Oracle10g 数据库。
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.
这两个表使用 ID_DEBTOR 字段连接。
I want to update the T_ELEMENT.INSURER value with the associated T_DEBTOR.HEADER only if HEADER is not null. In others words:
仅当 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!
I tried to use the following SQL query:
我尝试使用以下 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);
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.
此查询适用于链接到 HEADER 不为空的债务人的所有元素。但是，当 T_DEBTOR.HEADER 为空时，此查询将 T_ELEMENT.INSURER 设置为空，这是不正确的。
ie:
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:
编辑，关于 Brian Storrar 的回答：
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;
采纳答案by Rob van Wijk
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:
使用您当前的更新语句，问题就变得清晰了：“未更新”值设置为 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.
使用bypass 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.
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.
问候，罗伯。
回答by Vincent Malgrat
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 :
从 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;
Cheers,
干杯，
--
Vincent
——
文森特
回答by Romain Linsolas
I've found a solution to solve my problem (the where clause is added):
我找到了解决我的问题的解决方案（添加了 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);
If you have a better solution, do not hesitate to post it!
如果您有更好的解决方案，请不要犹豫，发布它！
回答by stjohnroe
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.
或类似的东西，这有点没有选择性，但我认为它会做你想要的。
回答by Stephen ODonnell
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!).
这将创建您的表（提示 - 如果您可以为您的示例发布 SQL，这将非常有用！）。
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
回答by H20rider
@Rob Thanks for the /*+ bypass_ujvc */ Tip. I have a couple cases where I need to use this. I wish my DBA told be able this. There are a couple times I had to create a cursor to get around this.
@Rob 感谢 /*+ bypass_ujvc */ 提示。我有几个案例需要使用它。我希望我的 DBA 能够做到这一点。有几次我不得不创建一个游标来解决这个问题。
回答by John Smithers
You could use the SQL Case statement, to distinguish when HEADER is null and when it has a value:
http://www.tizag.com/sqlTutorial/sqlcase.php
您可以使用 SQL Case 语句来区分 HEADER 何时为空以及何时具有值：http:
//www.tizag.com/sqlTutorial/sqlcase.php
回答by Hooloovoo
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;
