title: mysql UPDATE says column cannot be null. Why is it null?
body: <p>I have a join table called <code>carriers_rects</code> that looks like this: </p>
  
  <pre><code>+------------+------------------+------+-----+---------+----------------+
  | Field      | Type             | Null | Key | Default | Extra          |
  +------------+------------------+------+-----+---------+----------------+
  | id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
  | carrier_id | int(11) unsigned | NO   |     | NULL    |                |
  | rect_id    | int(11) unsigned | NO   |     | NULL    |                |
  +------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>I also have a <code>rects</code> table that looks like this: </p>
  
  <pre><code>+---------+-------------+------+-----+----------+----------------+
  | Field   | Type        | Null | Key | Default  | Extra          |
  +---------+-------------+------+-----+----------+----------------+
  | id      | int(11)     | NO   | PRI | NULL     | auto_increment |
  | name    | varchar(54) | NO   |     | new rect |                |
  | width   | varchar(54) | NO   |     | NULL     |                |
  | length  | varchar(54) | NO   |     | NULL     |                |
  | rounded | tinyint(1)  | NO   |     | NULL     |                |
  | xx      | varchar(54) | NO   |     | NULL     |                |
  | yy      | varchar(54) | NO   |     | NULL     |                |
  | height  | varchar(54) | NO   |     | NULL     |                |
  +---------+-------------+------+-----+----------+----------------+
  </code></pre>
  
  <p>I am trying to add a <code>case_id</code> column to <code>rects</code> and just make it a <code>one-to-many</code> relationship and kill the <code>carriers_rects</code> table. We are moving our DB and we never used the <code>many-to-many</code> relationship. </p>
  
  <p>So I added the <code>case_id</code> column to <code>rects</code>:</p>
  
  <pre><code>alter table rects add case_id int(11) not null;
  </code></pre>
  
  <p>Then I tried to update the <code>case_id</code> on the rects with all the <code>case_id</code>'s that would match from the <code>carriers_rects</code> table. </p>
  
  <pre><code>update rects set case_id = (select carrier_id from carriers_rects where rect_id = id);
  </code></pre>
  
  <p>I am getting <code>column case_id cannot be null</code>. </p>
  
  <p>I tested to see if there where any nulls and I can't seem to find any. </p>
  
  <pre><code>select * from (select * from carriers_rects where rect_id IN(select id from rects)) `b` where id is null;
  </code></pre>
  
  <p>I also tried it the other way around because honestly I am a little confused. </p>
  
  <pre><code>select id from rects where id IN(select rect_id from carriers_rects)
  </code></pre>
  
  <p>Clearly I am not a <code>sql</code> genius. But would love to be schooled here. 
  Not sure why I am getting the error mentioned above.</p>