Oracle Update with Join - ORA-01779

Question

Good morning,

I am trying to update a column using information from another table using ORACLE. However, I am receiving the error "ORA-01779: it is not possible to modify a column that changes a table not preserved by the key".

Following script used:

update (
    select AP.NR_ATENDIMENTO,
    PPR.NR_PRESCRICAO,
    PP.CD_MEDICO_EXECUTOR,
    PP.CD_FUNCAO,
    PPR.CD_MEDICO_EXEC,
    PPR.NR_SEQUENCIA,
    PP.NR_INTERNO_CONTA,
    lp.CD_MEDICO_RESP
    from atendimento_paciente ap 
    inner join PRESCR_MEDICA pm on
        AP.NR_ATENDIMENTO = PM.NR_ATENDIMENTO
    INNER JOIN PRESCR_PROCEDIMENTO PPR ON
        PM.NR_PRESCRICAO = PPR.NR_PRESCRICAO
    INNER JOIN PROCEDIMENTO_PACIENTE PP ON
        PPR.NR_PRESCRICAO = PP.NR_PRESCRICAO
        AND PPR.NR_SEQUENCIA = PP.NR_SEQUENCIA_PRESCRICAO
    LEFT JOIN LAUDO_PACIENTE LP ON
        PPR.NR_PRESCRICAO = LP.NR_PRESCRICAO
        AND PPR.NR_SEQUENCIA = LP.NR_SEQ_PRESCRICAO
    WHERE 0 = 0
    and PPR.CD_MEDICO_EXEC is not null
    and PP.CD_MEDICO_EXECUTOR IS NULL
    AND PPR.CD_SETOR_ATENDIMENTO <> 78
    AND PP.NR_INTERNO_CONTA IS NOT NULL
    AND AP.DT_ENTRADA >= '01/04/2018'
    AND PP.CD_FUNCAO <> 900
    and AP.NR_ATENDIMENTO = 297106
    AND LP.CD_MEDICO_RESP IS NOT NULL

) Updt set updt.CD_MEDICO_EXECUTOR = Updt.CD_MEDICO_RESP; commit;

Someone has a tip on how to do this update?

Atte;

Answer 1

One way to solve is by using MERGE oracle to do this.

It would look something like:

MERGE INTO PROCEDIMENTO_PACIENTE PP
USING (select PPR.NR_PRESCRICAO, PPR.NR_SEQUENCIA, LP.CD_MEDICO_RESP
    from atendimento_paciente ap 
    inner join PRESCR_MEDICA pm on
        AP.NR_ATENDIMENTO = PM.NR_ATENDIMENTO
    INNER JOIN PRESCR_PROCEDIMENTO PPR ON
        PM.NR_PRESCRICAO = PPR.NR_PRESCRICAO
    LEFT JOIN LAUDO_PACIENTE LP ON
        PPR.NR_PRESCRICAO = LP.NR_PRESCRICAO
        AND PPR.NR_SEQUENCIA = LP.NR_SEQ_PRESCRICAO
    WHERE PPR.CD_MEDICO_EXEC is not null
        AND PPR.CD_SETOR_ATENDIMENTO <> 78
        AND AP.DT_ENTRADA >= '01/04/2018'
        and AP.NR_ATENDIMENTO = 297106
        AND LP.CD_MEDICO_RESP IS NOT NULL
) Updt
ON (PP.NR_PRESCRICAO = Updt.NR_PRESCRICAO
    AND PP.NR_SEQUENCIA_PRESCRICAO = Updt.NR_SEQUENCIA
    AND PP.CD_MEDICO_EXECUTOR IS NULL
    AND PP.NR_INTERNO_CONTA IS NOT NULL
    AND PP.CD_FUNCAO <> 900
)
WHEN MATCHED THEN 
    UPDATE SET PP.CD_MEDICO_EXECUTOR = Updt.CD_MEDICO_RESP;

See if it answers.

Answer 2

Good afternoon, Merge answered yes.

I just had to make a small change because I was presenting the following message:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "PP"." CD_MEDICO_EXECUTOR"

In case someone finds the same problem I do, follow code:

MERGE INTO PROCEDIMENTO_PACIENTE PP
USING (
    select PPR.NR_PRESCRICAO, PPR.NR_SEQUENCIA, NVL(LP.CD_MEDICO_RESP, PPR.CD_MEDICO_EXEC) CD_MEDICO_RESP
    from atendimento_paciente ap 
    inner join PRESCR_MEDICA pm on
        AP.NR_ATENDIMENTO = PM.NR_ATENDIMENTO
    INNER JOIN PRESCR_PROCEDIMENTO PPR ON
        PM.NR_PRESCRICAO = PPR.NR_PRESCRICAO
    INNER JOIN PROCEDIMENTO_PACIENTE PP1 ON
        PPR.NR_PRESCRICAO = PP1.NR_PRESCRICAO
        AND PPR.NR_SEQUENCIA = PP1.NR_SEQUENCIA_PRESCRICAO
    LEFT JOIN LAUDO_PACIENTE LP ON
        PPR.NR_PRESCRICAO = LP.NR_PRESCRICAO
        AND PPR.NR_SEQUENCIA = LP.NR_SEQ_PRESCRICAO
    WHERE PPR.CD_MEDICO_EXEC is not null
        AND PPR.CD_SETOR_ATENDIMENTO <> 78
        AND AP.DT_ENTRADA >= '01/04/2018'
        AND PP1.CD_MEDICO_EXECUTOR IS NULL
        AND PP1.NR_INTERNO_CONTA IS NOT NULL
        AND PP1.CD_FUNCAO <> 900
) Updt
ON (PP.NR_PRESCRICAO = Updt.NR_PRESCRICAO
    AND PP.NR_SEQUENCIA_PRESCRICAO = Updt.NR_SEQUENCIA  
)
WHEN MATCHED THEN 
    UPDATE SET PP.CD_MEDICO_EXECUTOR = Updt.CD_MEDICO_RESP;

Thank you so much for your help.