Error using WITH clause and UPDATE in MySql-CodePudding

i tried to run this query:

WITH updateables AS (
    SELECT id_product, quantity
    FROM products_order
    WHERE id_order = 1239 AND state = 10
)
UPDATE product
SET product.stock = updateables.quantity
WHERE product.id_product = updateables.id_product```

But this trows:

Error Code: 1054. Unknown column 'updateables.id_product' in 'where clause```

CodePudding user response：

Defining a CTE using the WITH clause does not implicitly join that CTE to other tables in your query.

You would need to use a JOIN in your query to the CTE:

WITH updateables AS (
    SELECT id_product, quantity
    FROM products_order
    WHERE id_order = 1239 AND state = 10
)
UPDATE product JOIN updateables ON product.id_product = updateables.id_product
SET product.stock = updateables.quantity;