2

I need to get IDs variant and delete all records except those IDs.

My SQL query looks like this:

DELETE FROM catalog_variant WHERE id NOT IN (
  SELECT v.id FROM catalog_variant v
  JOIN catalog_product p ON p.id=v.id_product
  JOIN catalog_category_product_mm cpmm ON p.id=cpmm.id_product
  JOIN catalog_category c ON c.id=cpmm.id_category
  JOIN catalog_category_parameter_mm cparmm ON c.id=cparmm.id_category
  JOIN catalog_parameter par ON par.id=cparmm.id_parameter

  WHERE p.id=9 AND par.type=2 AND c.id IN(
    SELECT c.id FROM catalog_category_product_mm cpmm 
    JOIN catalog_category c ON c.id=cpmm.id_category 
    WHERE cpmm.id_product=9
  )
  GROUP BY v.id
);

I get this error:

You can't specify target table 'catalog_variant' for update in FROM clause

Can you help me with how to do it correctly?

Anders
  • 8,307
  • 9
  • 56
  • 88
kevas
  • 551
  • 2
  • 7
  • 22

2 Answers2

1

You can wrap the inner query inside a temporary table, which I have called temp below. This will avoid the annoying target table error message you have been seeing.

DELETE FROM catalog_variant WHERE id NOT IN
(
    SELECT temp.id FROM
    (
        SELECT v.id AS id FROM catalog_variant v
        JOIN catalog_product p ON p.id=v.id_product
        JOIN catalog_category_product_mm cpmm ON p.id=cpmm.id_product
        JOIN catalog_category c ON c.id=cpmm.id_category
        JOIN catalog_category_parameter_mm cparmm ON c.id=cparmm.id_category
        JOIN catalog_parameter par ON par.id=cparmm.id_parameter
        WHERE p.id=9 AND par.type=2 AND c.id IN
        (
            SELECT c.id FROM catalog_category_product_mm cpmm 
            JOIN catalog_category c ON c.id=cpmm.id_category 
            WHERE cpmm.id_product=9
        )
        GROUP BY v.id
    ) temp
);

By the way, there are already some pretty good resources here on Stack Overflow which cover this problem, such as this one.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

You can use a delete statement with a join.

Or you could put the IDs into a temporary table first.

Community
  • 1
  • 1