我正在使用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.
问候,抢.
