Question 410108 in Mysql Question posted in
The official MySQL documentation can be found here.

Why do the CTEs work different from the Subqueries in MySQL?

I try deleting all the goods, whose type_name starts with D.

Let’s say I wanna delete goods from the types of dentistry, delicacies, darts_game.
So I check whether the type is in the list d_goods_ids (type is a FK of good_type_id).

WITH d_goods_ids AS (SELECT good_type_id FROM GoodTypes 
                 WHERE GoodTypes.good_type_name LIKE "d%")

DELETE FROM Goods
WHERE type IN d_goods_ids;

SELECT * FROM Goods;

But MySQL raises an error:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd_goods_ids; SELECT * FROM Goods' at line 5

Whereas if I use a subquery, everything works perfectly:

DELETE FROM Goods
WHERE type IN (SELECT good_type_id FROM GoodTypes 
               WHERE GoodTypes.good_type_name LIKE "d%");

SELECT * FROM Goods;

Shouldn’t both variants work the same in this context?

Tags:

2

Answers


  1. 0

    You can reference a CTE in a subquery, but not like you are trying to do it.

    WITH d_goods_ids AS (SELECT good_type_id FROM GoodTypes
                 WHERE GoodTypes.good_type_name LIKE "d%")

DELETE FROM Goods
WHERE type IN (SELECT good_type_id FROM d_goods_ids);

    In this example, the CTE is in a subquery, referenced in a FROM clause as if it’s a table name.

    https://dev.mysql.com/doc/refman/8.4/en/with.html says:

    cte_name names a single common table expression and can be used as a table reference in the statement containing the WITH clause.

    (bold emphasis is mine)

    So you can use a CTE where you would otherwise use a table reference.

    Another way of thinking about the syntax is that you can’t substitute an isolated table name for a subquery.

    In other words, the following example is not a valid IN predicate, for the same reason using a CTE in that place is not valid:

    DELETE FROM Goods
WHERE type IN GoodTypes;
    Login or Signup to reply.
  2. 0

    Another way to properly use the CTE in the DELETE can look like this

    WITH d_goods_ids AS (
  SELECT good_type_id
  FROM GoodTypes
  WHERE GoodTypes.good_type_name LIKE "d%"
)

DELETE Goods
FROM Goods
JOIN d_goods_ids ON d_goods_ids.good_type_id = Goods.type;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search