Mysql UPDATE says the column cannot be null. Why is this null?

Question

I have a connection table with a name carriers_rectsthat 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 table rectsthat 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 column case_idto rectsand just make it a relation one-to-manyand kill the table carriers_rects. We are moving our database and we have never used a relationship many-to-many.

So, I added a column case_idto rects:

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

Then I tried updating case_idon patches with all case_idthat would match the table carriers_rects.

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

I get it column case_id cannot be null.

I tested if there are any zeros there, and I can not find them.

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

I also tried it differently because, to be honest, I'm a little confused.

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

, sql. . , , .

Answer 1

rect_id = id. carriers_rects ( carriers_rects.rect_id = carriers_rects.id), null. rect_id = rects.id

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

Answer 2

, update-join, ,

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;