Jump to content

click.png

click.png

click.png

click.png

click.png

click.png

click.png

click.png

click.png

click.png click.png click.png

click.png

click.png

click.png

ajuda com update


Dfox
Go to solution Solved by Dfox,

Recommended Posts

bom dia a todos,

tenho o seguinte comando sql

update C000050 QRC
set QRC.CODCONTA = (select RC.CODIGO
                    from C000049 RC
                    where substring(RC.CODIGO from 1 for 10) = QRC.CODCONTA and
                          char_length(QRC.CODCONTA) = 10)
where char_length(QRC.CODCONTA) = 10

porem da erro por ter registros repetidos multiple rows in singleton select.

alguém teria uma solução?

Link to comment
Share on other sites

1 hora atrás, Dfox disse:
select RC.CODIGO
                    from C000049 RC
                    where substring(RC.CODIGO from 1 for 10) = QRC.CODCONTA and
                          char_length(QRC.CODCONTA) = 10

nesse select esta retornando mais de um resultado, vc deve filtrar melhor para que esse select retorne apenas um resultado.

eu usaria:

update C000050 QRC
set QRC.CODCONTA = (select first 1 RC.CODIGO
                    from C000049 RC
                    where substring(RC.CODIGO from 1 for 10) = QRC.CODCONTA and
                          char_length(QRC.CODCONTA) = 10)
where char_length(QRC.CODCONTA) = 10

ai ele traria apenas um resultado, mas cuidado com isso, nada garante que será o registro que vc precisa, então de uma olhada melhor nos filtros

Edited by datilas
Link to comment
Share on other sites

  • Solution

Obriga deu certo mas ficou muito lento então fiz assim com duas formas

opção 1

merge into C000050 QRC
using (select RC.CODIGO
       from C000049 RC) as RC
on substring(RC.CODIGO from 1 for 10) = QRC.CODCONTA and char_length(QRC.CODCONTA) = 10
when matched then
    update set QRC.CODCONTA = RC.CODIGO

opção 2

execute block
as
  declare variable rccodigo varchar(15);
begin
for select RC.CODIGO from C000049 RC, C000050 QRC where substring(RC.CODIGO from 1 for 10) = QRC.CODCONTA and char_length(QRC.CODCONTA) = 10
    into :rccodigo do
    begin
       update C000050 set codconta = :rccodigo where codconta = substring(:rccodigo from 1 for 10);
    end
end;

ficou super rápido.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.