title: Multi-column update from SAME table, different rows, in Oracle, with not-null column: Receiving error 01407
body: <p>I'm getting an error I do not understand.</p>
  
  <p>I have an Oracle database (11g, I think) with a table that has a multi-column unique identifier. It's a transaction table, taking a before and after snapshot of the data from a different table. So far so good, that's all working fine.</p>
  
  <p>We had a bug in our code and I'm trying to fix the data now that the bug is fixed. The bug resulted in the 'after' snapshot having two columns zeroed out in a circumstance where they should have kept their 'before'. So I'm going to update the 'after' snapshot (identified by a transaction type number) to have the same values in those two columns.</p>
  
  <p>I've found all sorts of great help on the multi-column update thing, and I <em>think</em> I have it, except...it generates an error that it cannot update a not-null column to null, when there is no way I can see a null getting in there. Obviously, I am doing something wrong; I just can't tell what. </p>
  
  <p>In some cases, one column can be null - there's an object type column that defines whether that record can have a null value.</p>
  
  <p>The table has this data in it, say:</p>
  
  <pre><code>object_name      object_type   trans_type   trans_date   quantity    actual_cost
  no-quantity      1             1            04/16/2014   {null}      20.00
  no-quantity      1             9            04/16/2014   {null}       0.00
  needs quantity   2             1            04/16/2014   3           15.00
  needs quantity   2             9            04/16/2014   0            0.00 
  </code></pre>
  
  <p>So I need to update the second row (no-quantity, trans_type 9) to actual_cost of 20; quantity can stay null. And I need the 4th row to be updated to quantity of 3, actual_cost of 15.</p>
  
  <p>Here is the query I'm trying to run that is failing - create and insert statements are below:</p>
  
  <pre><code>update  demo_table new 
  set     (quantity, actual_cost) = 
          (
          SELECT  quantity, actual_cost
          FROM    demo_table old
          WHERE   old.object_name = new.object_name
              and old.object_type = new.object_type
              and old.trans_date = new.trans_date
              and old.trans_type = 1
              and new.trans_type = 9
  )
  </code></pre>
  
  <p>When I run this I get:</p>
  
  <p>ORA-01407: cannot update ("myschema"."DEMO_TABLE"."ACTUAL_COST") to NULL</p>
  
  <p>(I've tried the other way - update (select col1, col2, etc) - and the table's not set up right for it. Too bad, because that way looked easier to understand...)</p>
  
  <p>Do I maybe need to have an outer where too? The other posts didn't indicate that I did, and I don't know how I would formulate it anyway.</p>
  
  <p>I even get the not-null error on this attempt:</p>
  
  <pre><code>update  demo_table new 
  set     (actual_cost) = 
          (
          SELECT  actual_cost
          FROM    demo_table old
          WHERE   old.object_name = new.object_name
              and old.object_type = new.object_type
              and old.trans_date = new.trans_date
              and old.trans_type = 1
              and new.trans_type = 9
              and old.object_type = 2
  )
  </code></pre>
  
  <p>object_type of 2 doesn't have a null quantity, and anyway I'm not trying quantity in this one...</p>
  
  <p>The select statement I run to check the actual where clause is correct looks like this:</p>
  
  <pre><code>SELECT  old.object_name as old_name, old.object_type as old_type, old.trans_type as old_trans, old.trans_date as old_date,
      new.object_name as new_name, new.object_type as new_type, new.trans_type as new_trans, new.trans_date as new_date,
      old.quantity as old_quantity, old.actual_cost as old_cost, new.quantity as new_quantity, new.actual_cost as new_cost
  FROM    demo_table old, demo_table new
  WHERE    old.object_name = new.object_name
      and old.object_type = new.object_type
      and old.trans_date = new.trans_date
      and old.trans_type = 1
      and new.trans_type = 9
  </code></pre>
  
  <p>That gets the right values, the right old/new fields, only 2 rows returned, as I expect.</p>
  
  <p>Here are my create-and-insert statements:</p>
  
  <pre><code>create table demo_table (
      object_name varchar2(30) not null,
      object_type number(3) not null,
      trans_type number(3) not null,
      trans_date timestamp(6) not null,
      quantity number(3),
      actual_cost number(17,2) not null
  )
  
  insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
  values (
  'no-quantity', 1, 1, '16-APR-14', null, 20
  )
  
  insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
  values (
  'no-quantity', 1, 9, '16-APR-14', null, 0
  )
  
  insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
  values (
  'needs quantity', 2, 1, '16-APR-14', 3, 15
  )
  
  insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
  values (
  'needs quantity', 2, 9, '16-APR-14', 0, 0
  )
  </code></pre>
  
  <p>I hope my problem is clear. I did look around a lot, but I couldn't find anything that looked like it matched quite closely enough. The same-table thing is not really covered, and the not-nullable column is not really covered. (Or rather, it is, but I couldn't see how the problems described affected my situation.)</p>
  
  <p>I know the table setup is, shall we say, far from ideal. Can't fix it, tonight.</p>