我正在使用 Oracle 10g数据库。
我有以下两个表：
T_DEBTOR : - ID_DEBTOR - HEADER T_ELEMENT : - ID_ELEMENT - ID_DEBTOR - INSURER
使用ID_DEBTOR字段将这两个表连接在一起。
仅当HEADER不为null时，我才想使用关联的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的债务人链接的所有元素。但是，当T_DEBTOR.HEADER为null时，此查询会将T_ELEMENT.INSURER设置为null，这是不正确的。
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
我的查询出了什么问题？
编辑，关于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, 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：
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是唯一的。
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.
问候，罗布。
