SQL Insert into table new rows foreach field in same table

Question

I have a database of categories, sub-categories and products. Many sub-categories and therefore their products weren't adopted by the parent categories so I'm trying to use SQL to fix this but I'm coming across some issues.

Said table has three columns; id_category, id_product, position (they are all ints) In this table every time a product is in a category, it's repeated for that id_product for each id_category of given categories. Whether that be parent or sub-category.

As for an example, we can say the child category is 12 while the parent one is 143

So far what I have tried is

SELECT id_product FROM category_products WHERE id_category = 12

This does give me the products I am interested to make new rows but I cannot manage to make the INSERT statement work.

Secondly, position is also an issue as I need to select the last, highest number and for each field add a +1 to it, as it is the position of the product in the category.

What I am looking for is basically:

1. Take id_product where category = 12
2. For each id_product taken make a row where category is equal to 143
3. Take highest int in position where category = 143 and do +1 to it

Therefore we have something like this:

+============+=============+==========+
| id_product | id_category | position |
+============+=============+==========+
| 190        | 12          | 10       |
+------------+-------------+----------+
| 191        | 12          | 11       |
+------------+-------------+----------+
| 230        | 12          | 12       |
+------------+-------------+----------+
| 15         | 143         | 12       |
+------------+-------------+----------+
| 150        | 143         | 50       |
+------------+-------------+----------+

AFTER THE SQL IT WOULD BE LIKE

+============+=============+==========+
| id_product | id_category | position |
+============+=============+==========+
| 190        | 12          | 10       |
+------------+-------------+----------+
| 191        | 12          | 11       |
+------------+-------------+----------+
| 230        | 12          | 12       |
+------------+-------------+----------+
| 15         | 143         | 12       |
+------------+-------------+----------+
| 150        | 143         | 50       |
+------------+-------------+----------+
| 190        | 143         | 51       |
+------------+-------------+----------+
| 191        | 143         | 52       |
+------------+-------------+----------+
| 230        | 143         | 53       |
+------------+-------------+----------+

I tried several different syntaxes and everything but it's only returning errors to me all the time. (This is done in PHPMyAdmin by the way).

Answer 1

In case MySQL 8.0 or later you can use next query:

INSERT IGNORE INTO products
SELECT 
    id_product,
    143 as id_category,
    (
       SELECT MAX(position) 
       FROM products
       WHERE id_category = 143
     ) + 
     (row_number() over (order by id_product)) as position
FROM products
WHERE id_category = 12;

Result from SQLize.online:

+============+=============+==========+
| id_product | id_category | position |
+============+=============+==========+
| 190        | 12          | 10       |
+------------+-------------+----------+
| 191        | 12          | 11       |
+------------+-------------+----------+
| 230        | 12          | 12       |
+------------+-------------+----------+
| 15         | 143         | 12       |
+------------+-------------+----------+
| 150        | 143         | 50       |
+------------+-------------+----------+
| 190        | 143         | 51       |
+------------+-------------+----------+
| 191        | 143         | 52       |
+------------+-------------+----------+
| 230        | 143         | 53       |
+------------+-------------+----------+