SQL UPDATE rows on same table with parent-child relationship

Question

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.

Barmar · Accepted Answer · 2024-05-22 15:35:29Z

0

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

answered May 22, 2024 at 15:35

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tb517 Over a year ago

This works perfect, thank you. However, is there a way to write this where the JOIN is included after the WHERE clause? I'm using JPA/Hibernate and there are some restrictions when writing the query. It will not let me have two references of the same table for some reason.

Barmar Over a year ago

No, SQL syntax is not that flexible. The order of clauses is precisely defined.

Barmar Over a year ago

Are you sure it's set up for MySQL? Different SQL dialects have different ways of implementing joins in UPDATE queries.

tb517 Over a year ago

Good question. I am fairly new to this so I would not be surprised if it is not set up for that. I am trying to use the answer referenced here: stackoverflow.com/questions/39621608/…

Barmar Over a year ago

I suggest you ask a new question with the jpa tag so someone familiar can help you with converting this to its syntax.

Collectives™ on Stack Overflow

SQL UPDATE rows on same table with parent-child relationship

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

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

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

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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