mysql UPDATE says column cannot be null. Why is it null?

Question

I have a join table called carriers_rects that looks like this:

+------------+------------------+------+-----+---------+----------------+
| 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    |                |
+------------+------------------+------+-----+---------+----------------+

I also have a rects table that looks like this:

+---------+-------------+------+-----+----------+----------------+
| 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     |                |
+---------+-------------+------+-----+----------+----------------+

I am trying to add a case_id column to rects and just make it a one-to-many relationship and kill the carriers_rects table. We are moving our DB and we never used the many-to-many relationship.

So I added the case_id column to rects:

alter table rects add case_id int(11) not null;

Then I tried to update the case_id on the rects with all the case_id's that would match from the carriers_rects table.

update rects set case_id = (select carrier_id from carriers_rects where rect_id = id);

I am getting column case_id cannot be null.

I tested to see if there where any nulls and I can't seem to find any.

select * from (select * from carriers_rects where rect_id IN(select id from rects)) `b` where id is null;

I also tried it the other way around because honestly I am a little confused.

select id from rects where id IN(select rect_id from carriers_rects)

Clearly I am not a sql genius. But would love to be schooled here. Not sure why I am getting the error mentioned above.

Answer 1

What if you change to a update-join syntax rather than using subquery like

update rects r
join carriers_rects cr on cr.rect_id = r.id
set r.case_id = cr.carrier_id
where cr.carrier_id is not null;