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

Mysql – Updating all records with parent rows value?

I have a list of items. Original items were set a property, but there was a bug in the app where child rows didn’t inherit the property.

Here is what I’ve tried to fix it:

UPDATE queue_items SET property_value = (
    SELECT property_value FROM queue_items WHERE id = origin_id
    ) WHERE property_value IS NULL OR LENGTH(property_value) < 5 AND origin_id IS NOT NULL;

But I get: [HY000][1093] You can’t specify target table ‘queue_items’ for update in FROM clause

Tags:

2

Answers


  1. 0

    The query should be similar to:

    WITH RECURSIVE
cte AS (
  SELECT id, parent, property
  FROM test
  WHERE parent IS NULL
  UNION ALL
  SELECT test.id, test.parent, COALESCE(test.property, cte.property)
  FROM cte
  JOIN test ON cte.id = test.parent
)
UPDATE test
JOIN cte USING (id)
SET test.property = cte.property;

    fiddle

    If the condition is more complex than simple "replace NULL with parent value" then use according CASE expression instead of COALESCE().

    Login or Signup to reply.
  2. 0

    Main issue "You can’t specify target table…"

    This is a ridiculous problem specific to MySQL. You cannot reference the updated table in a subquery directly, so

    SET property_value = (... FROM queue_items ...)

    must be changed to something like

    SET property_value = (... FROM (SELECT * FROM queue_items) x ...)

    Be aware though, that there are other issues in your update statement.

    Secondary issue #1:

    SELECT property_value FROM queue_items WHERE id = origin_id

    How many rows do you think you’ll get with this? You don’t specify the columns’ tables, so the local table is taken, which is

    SELECT property_value FROM queue_items WHERE queue_items.id = queue_items.origin_id

    What you really want to do is to have one of the columns be taken from the updated row. Use table aliases to differentiate between updated row and parent row.

    Secondary issue #2:

    WHERE property_value IS NULL OR LENGTH(property_value) < 5 AND origin_id IS NOT NULL

    AND has precedence over OR. So what you have is

    WHERE property_value IS NULL
   OR (LENGTH(property_value) < 5 AND origin_id IS NOT NULL)

    while you probably want

    WHERE (property_value IS NULL OR LENGTH(property_value) < 5)
  AND origin_id IS NOT NULL

    Final statement:

    UPDATE queue_items
  SET property_value = 
  (
    SELECT parent_row.property_value
    FROM (SELECT * FROM queue_items) parent_row
    WHERE parent_row.id = queue_items.origin_id
  )
WHERE (property_value IS NULL OR LENGTH(property_value) < 5)
  AND origin_id IS NOT NULL;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search