Insufficient privileges when connecting updates

Question

Scenario: we create records for requests that must be approved by the manager. While waiting, the manager changes (updated overnight from the HR channels). We need to update requests to indicate a new manager.

Here's an abridged version of the request, which will , should do this:

update (select grw.approver_user_id, gup.supervisor_id from gs3.user_role gur join gsu.user_profile gup on gur.user_id = gup.user_id join gs3.request_workflow grw on gur.user_role_id = grw.user_role_id and gup.supervisor_id != grw.approver_user_id -- records with new mgr where grw.auth_status_cd = 'SUBMITTED') -- reapprovals currently open set grw.approver_id = gup.supervisor_id; 

Problem: The account executing this request has only read privileges on gsu.user_profile .

The internal selection works fine and returns all the rows that I need to update ... but even if I don't update gup.supervisor_id , it seems I need to have write access to this table. If I execute this as a user who has write access to gsu.user_profile , the update will succeed.

Is there a logical reason for this? I would prefer not to grant permissions for an account that he does not need.

Thanks!

Update

Accepting Thomas’s answer ... although he doesn’t actually answer my question about why the account that is connecting to the update needs update privileges for the table that it is not updating, I see the logic in the saying “Do not use updates They are not an ISO standard. "

This is a shame because the difference between what I have and Thomas’s proposal is that I don’t have any nested samples. If someone knows the standard ISO method for performing such a request without nested selections, I would like to know!

Thank you Thomas!

Answer 1

Try using an ISO-approved format for the Update statement and see if this works. ISO does not provide for the use of a compound directly in an update report. Rather, you can only use joins through subqueries.

In addition, this may illustrate some problems in your original Update document if, for example, the subquery used to set approver_id returns more than one row, which will obviously throw an exception, and you will need to determine how to find one and only one supervisor_id to be set for each row.

 Update gs3.request_workflow Set approver_id = ( Select gup.supervisor_id From gs3.user_role As gur Join gsu.user_profile As gup On gur.user_id = gup.user_id Where gup.user_role_id = gs3.request_workflow.user_role_id And gup.supervisor_id != grw.approver_user_id ) Where auth_status_cd = 'SUBMITTED' And Exists ( Select 1 From gs3.user_role As gur Join gsu.user_profile As gup On gur.user_id = gup.user_id Where gup.user_role_id = gs3.request_workflow.user_role_id And gup.supervisor_id != grw.approver_user_id )