Oracle Update with Join - ORA-01779

Navigation
0

Good morning,

I'm trying to do an update on a column using information from another table using ORACLE. However, I'm getting the error "ORA-01779: Can not modify a column that maps 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;

Does anyone have a tip on how to do this update?

atte;

    
asked by anonymous 10.07.2018 / 15:45

2 answers

0

One way to work around is to use oracle MERGE 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 you can.

    
16.07.2018 / 18:06
0

Good afternoon, the Merge did.

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

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

If someone encounters the same problem as me, code follows: 

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 very much for the help.

    
24.07.2018 / 18:48
dsPIC - How to receive a UART string only when calling a function? Receiving HTTP POST Json with PHP MYSQL PDO