Oracle database: bulk update generate ORA-01779

byMR

June 7, 2023

I try to update on bulk all hardcoded values in a table with value from another table on an Oracle database.

Table Request

request_id
service_assigned
client_id

Table User

user_id
service

Table lnk_request_user

assignee_id (link to user_id from user table)
request_id

        update
        (
        select req.SERVICE_ASSIGNED as OLD, users.service as NEW
            from REQUEST req, LNK_REQUEST_ASSIGNEE assignees, USER users
            where
                req.request_id = assignees.request_id
                and
                users.user_id = assignees.assignee_id
                and
                req.client_id=9999         
        ) request 
        set request.OLD = request.NEW;

Oracle return:

SQL Error: ORA-01779: cannot modify a column which maps to a non
key-preserved table

What’s does it means? How can I fix my query?

>Solution :

I’d use a merge like below:

merge into REQUEST tgt
using (
  select req.request_id, users.service SERVICE_ASSIGNED
  from REQUEST req, LNK_REQUEST_ASSIGNEE assignees, USER users
  where req.request_id = assignees.request_id
    and users.user_id = assignees.assignee_id
    and req.client_id=9999           
) src
on (tgt.request_id=src.request_id)
when matched then update set
  tgt.SERVICE_ASSIGNED=src.SERVICE_ASSIGNED;