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;
An UPDATE statement can include JOIN operations.
Oracle Update Statement with Join The answer is pretty straightforward: in Oracle this syntax of UPDATE statement with a JOIN is not supported.
First, specify the name of the table (t1) that you want to update in the UPDATE clause. Next, specify the new value for each column of the updated table. Then, again specify the table from which you want to update in the FROM clause.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With