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 |
With the query below I can successfully update all of the rows necessary:
UPDATE table AS t1 JOIN table AS t2 ON t1.parentItemId = t2.itemId SET t1.ownerId = ownerIdReq WHERE t2.workdId IN workdIdsReq
The above query does not work with JPA @Query and setting nativeQuery = true. It does not seem to like the fact that I am referencing the same table twice. I was wondering if there was another way I could write this.