UPDATE based on multiple overlapping conditions

I have a query which essentially transfers a job from one worker to another.

The query works fine, but I need to add one additional check:

• ensure the current job owner (in jobs) is the worker matching @owner and @teamId.

This is essentially the condition that I need to add:

...
AND jobs.owner = (SELECT userId FROM workers
                     WHERE workers.id = @ownerId and workers.teamId = @teamId)
...

To the query below:

UPDATE jobs
SET owner = workers.userId,
    role = @role
FROM workers
WHERE 
      workers.id = @newOwnerId
  AND workers.teamId = @teamId
  AND jobs.id = @jobId
  AND jobs.owningTeamId = @teamId

The problem is that if I add that additional check, it breaks the update since I am already filtering the workers table by the statement workers.id = @newOwnerId which is necessary to SET the new owner in jobs table. I think I might need a double join onto that table, but I’m not sure.

Answer

Doesn’t this work?

UPDATE jobs j
SET owner = w.userId,
    role = @role
FROM workers w
WHERE w.id = @newOwnerId AND
      w.teamId = @teamId AND
      j.id = @jobId AND
      j.owningTeamId = @teamId AND
      j.owner = (SELECT w2.userId
                 FROM workers w2
                 WHERE w2.id = @ownerId and w2.teamId = @teamId
                );

This appears to be what you are asking for.