MySQL: Update with sub query?

September 24, 2022

I’m trying to update all child objects to match their parents metadata value, but this query doesn’t seem to update anything? It reports 10 rows affected, but when I query for them they aren’t found.

Update Query:

UPDATE worker_items
SET metadata = (SELECT metadata FROM worker_items WHERE id = worker_items.origin_id)
WHERE depth != 0 AND metadata IS NULL
LIMIT 10;

Fetch query (which is empty)

SELECT * FROM worker_items WHERE metadata IS NOT NULL AND depth != 0

>Solution :

I don’t know how your data looks but you could try this,
to see if is working:

UPDATE 
    worker_items as w, worker_items as p
SET w.metadata = p.metadata
WHERE w.depth != 0 AND w.metadata IS NULL and p.id = w.worker_items.origin_id
LIMIT 10;