**Background**

I made a small table of 10 rows from a previous SELECT already ran (SavedAnimals).

I have a massive table (animals) which I would like to UPDATE using the rows with the same id as each row in my new table.

**What I have tried so far**

I can quickly SELECT the desired rows from the big table like this:

```
mysql> EXPLAIN SELECT * FROM animals WHERE ignored=0 and id IN (SELECT animal_id FROM SavedAnimals);

+------+--------------+-------------------------------+--------+---------------+---------+---------+----------------------------------------------------------+------+-------------+
| id   | select_type  | table                         | type   | possible_keys | key     | key_len | ref                                                      | rows | Extra       |
+------+--------------+-------------------------------+--------+---------------+---------+---------+----------------------------------------------------------+------+-------------+
|    1 | PRIMARY      | <subquery2>                   | ALL    | distinct_key  | NULL    | NULL    | NULL                                                     |   10 |             |
|    1 | PRIMARY      | animals                       | eq_ref | PRIMARY       | PRIMARY | 8       | db_staging.SavedAnimals.animal_id |    1 | Using where |
|    2 | MATERIALIZED | SavedAnimals | ALL    | NULL          | NULL    | NULL    | NULL                                                     |   10 |             |
+------+--------------+-------------------------------+--------+---------------+---------+---------+----------------------------------------------------------+------+-------------+

```

But the "same" command on the UPDATE is not quick:

```
mysql> EXPLAIN UPDATE animals SET ignored=1, ignored_when=CURRENT_TIMESTAMP WHERE ignored=0 and id IN (SELECT animal_id FROM SavedAnimals);
+------+--------------------+-------------------------------+-------+---------------+---------+---------+------+----------+-------------+
| id   | select_type        | table                         | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+------+--------------------+-------------------------------+-------+---------------+---------+---------+------+----------+-------------+
|    1 | PRIMARY            | animals                       | index | NULL          | PRIMARY | 8       | NULL | 34269464 | Using where |
|    2 | DEPENDENT SUBQUERY | SavedAnimals | ALL   | NULL          | NULL    | NULL    | NULL |       10 | Using where |
+------+--------------------+-------------------------------+-------+---------------+---------+---------+------+----------+-------------+
2 rows in set (0.00 sec)

```

The UPDATE command never finishes if I run it.

**QUESTION**

How do I make mariaDB run with the Materialized select\_type on the UPDATE like it does on the SELECT?

OR

Is there a totally separate way that I should approach this which would be quick?

**Notes**

Version: 10.3.23-MariaDB-log