MySQL: Update with sub query?-CodePudding

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

CodePudding user response：

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;