SQL Insertar en la tabla nuevas filas para cada campo en la misma tabla

Question

Tengo una base de datos de categorías, subcategorías y productos. Muchas subcategorías y, por lo tanto, sus productos no fueron adoptados por las categorías principales, así que estoy tratando de usar SQL para solucionar esto, pero me encuentro con algunos problemas.

Dicha tabla tiene tres columnas; id_category, id_product, position (todos son ints) En esta tabla, cada vez que un producto está en una categoría, se repite para ese id_product para cada id_category de categorías dadas. Ya sea padre o subcategoría.

Como ejemplo, podemos decir que la categoría secundaria es 12 mientras que la principal es 143

Hasta ahora lo que he intentado es

SELECT id_product FROM category_products WHERE id_category = 12

Esto me da los productos que me interesan para hacer nuevas filas, pero no puedo lograr que la instrucción INSERT funcione.

En segundo lugar, la posición también es un problema, ya que necesito seleccionar el último número más alto y para cada campo agregarle un +1, ya que es la posición del producto en la categoría.

Lo que estoy buscando es básicamente:

1. Tome id_product donde category = 12
2. Para cada id_product tomado, haga una fila donde la categoría sea igual a 143
3. Tome el int más alto en la posición donde categoría = 143 y haga +1

Por lo tanto tenemos algo como esto:

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

DESPUÉS DEL SQL SERÍA COMO

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

Probé varias sintaxis diferentes y todo, pero solo me devuelve errores todo el tiempo. (Esto se hace en PHPMyAdmin por cierto).

Answer 1

En caso de MySQL 8.0 o posterior, puede usar la siguiente consulta:

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;

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