Error in the subselect of an sql script to generate update script bringing more than one line and giving errors

Question

I have two tables, a partner of which some fields of cnpj_cpf are wrong and another that I went up with the corrected data, totaling I have approximately 1 million lines to solve, so I generated a script to generate a table with the update fields according to what I needed to do. I have a tgfpar table that I need to make the correction, I was able to find out what data is through the SELECT made, but when copying all the lines with the updates fields generated it gives an error that my sub-query inside the update Where is bringing more than one line. The logic to be followed is that if the field tgfpar.razaosocial = onepdv_fix.razaosocial and cnpj_cpf of the partner table other than the correction table.

SELECT  DISTINCT PAR.CODPARC,
        PAR.RAZAOSOCIAL,
        PAR.CGC_CPF AS CPF_ERRADO,
        B.RAZAOSOCIAL,
        B.CNPJ_CPF AS CPF_CORRETO, 
        'UPDATE TGFPAR SET CGC_CPF = '|| B.CNPJ_CPF || ' WHERE CGC_CPF <> (SELECT CNPJ_CPF FROM ONEPDV_CORRECAO OPDV, TGFPAR PAR WHERE PAR.CGC_CPF <> OPDV.CNPJ_CPF) AND RAZAOSOCIAL = (SELECT B.RAZAOSOCIAL FROM ONEPDV_CORRECAO B);'
FROM    ONEPDV_CORRECAO B
INNER JOIN TGFPAR PAR
        ON B.RAZAOSOCIAL = PAR.RAZAOSOCIAL
WHERE   B.GEO = 'GEO MG'
AND     B.CNPJ_CPF <> PAR.CGC_CPF;

it will bring as a response the script mounted line by line.

UPDATE TGFPAR SET CGC_CPF = 1234567891 WHERE CGC_CPF <> (SELECT CNPJ_CPF FROM ONEPDV_CORRECAO OPDV, TGFPAR PAR WHERE PAR.CGC_CPF <> OPDV.CNPJ_CPF) AND RAZAOSOCIAL = (SELECT B.RAZAOSOCIAL FROM ONEPDV_CORRECAO B);

Returns Error report - ORA-01427: Single-line sub-allowance returns more than one line

I need to update only the ones that cnpj_cpf are different from.

Thank you very much to those who help!

Answer 1

Without testing though, basic idea.

BEGIN
FOR R IN (SELECT DISTINCT PAR.CODPARC, 
                PAR.RAZAOSOCIAL, 
                PAR.CGC_CPF AS CPF_ERRADO, 
                B.RAZAOSOCIAL RAZAOSOCIALCH, 
                B.CNPJ_CPF AS CPF_CORRETO
         FROM ONEPDV_CORRECAO B 
           INNER JOIN TGFPAR PAR ON B.RAZAOSOCIAL = PAR.RAZAOSOCIAL
         WHERE B.GEO = 'GEO MG' 
         AND B.CNPJ_CPF <> PAR.CGC_CPF)
LOOP
 UPDATE TGFPAR SET CGC_CPF = R.CPF_CORRETO
 WHERE RAZAOSOCIAL = R.RAZAOSOCIALCH;
END LOOP;
END;