sqlmysqlsql-updateparent-child

SQL UPDATE rows on same table with parent-child relationship

Given the example table below (which is a very small-scale version), I am trying to update the owner name on a row based on the user input of a single ownerId and multiple workIds. However, there is some extra criteria where if a workId being updated has a parentItemId with associated children itemIds, those with children itemIds should be updated as well.

workId owner ownerId itemId parentItemId
1 Bob 23 123 123
2 Bob 23 234 123
3 Tom 35 345 123
4 Tom 35 789 789
5 Joe 44 567 789
6 Joe 44 678 789

For example, let's say as a user I provide the following update request, given there is another owner named John with an ownerId of 56: { ownerIdReq: 56, workIdsReq: [1] }

With that request, the table should update workId 1 to John, but also workId 2 and 3 as well, since workId 1 has an itemId of 123 which is a parentItemId, and workId 2 and 3 have parentItemId as 123. The table would update as follows:

workId owner ownerId itemId parentItemId
1 John 56 123 123
2 John 56 234 123
3 John 56 345 123
4 Tom 35 789 789
5 Joe 44 567 789
6 Joe 44 678 789

As for the SQL, it is pretty simple in terms of updating based on the workId:

UPDATE table SET ownerId = ownerIdReq WHERE workId IN workIdsReq

Where I run into issues is my thought process on updating the children which have a parentItemId of another row being updated. I can get the parentItemIds with something like:

SELECT parentItemId FROM table WHERE workId IN workIdsReq

I'm just not sure how to get the parentIds of the workIds being updated, then use those parentIds to look for itemIds and update them. I am having trouble translating this into SQL. Can this be done in a single query, or is that not possible? I would appreciate any tips or suggestions.

Solution

  • Use a self-JOIN to match the parent-child relationship.

    UPDATE table AS t1
JOIN table AS t2 ON t1.parentItemId = t2.itemId
SET t1.ownerId = ownerIdReq
WHERE t2.workdId IN workdIdsReq

    DEMO