title: What is wrong with my update statement with a join in Oracle?
body: <p>I am working with an <strong>Oracle</strong> 10g Database.</p>
  
  <p>I have the following two tables:</p>
  
  <pre><code>T_DEBTOR :
      - ID_DEBTOR
      - HEADER
  T_ELEMENT :
      - ID_ELEMENT
      - ID_DEBTOR
      - INSURER
  </code></pre>
  
  <p>These two tables are joined using the ID_DEBTOR field.</p>
  
  <p>I want to update the T_ELEMENT.INSURER value with the associated T_DEBTOR.HEADER only if HEADER is not null.
  In others words:</p>
  
  <pre><code>If T_DEBTOR.HEADER != null
      Then T_ELEMENT.INSURER = T_DEBTOR.HEADER
      Else T_ELEMENT.INSURER is not modified!
  </code></pre>
  
  <p>I tried to use the following SQL query:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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.</p>
  
  <p>ie:</p>
  
  <pre><code>If T_DEBTOR.HEADER != null
      Then T_ELEMENT.INSURER = T_DEBTOR.HEADER   --&gt; This part is OK
      Else T_ELEMENT.INSURER is set to null      --&gt; This part is NOT OK
  </code></pre>
  
  <p>What is wrong with my query?</p>
  
  <p><em>Edit, regarding the Brian Storrar answer:</em></p>
  
  <p>What I want to do is something like that:</p>
  
  <pre><code>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;
  </code></pre>