How to update with Oracle Inner Join

Asked

Viewed 37,379 times

3

Good afternoon ,

How do I update with Inner Join , because I need to change the field idnaoleitura = 24 to idnaoleitura = 0 where idrota in (35,45,48,53,60,68,70,79) , remembering the idrota this in another table.

The table calculo_leituras_ucb has the field idnaoleitura The calculo_data table has the idrota field The keys are the fields : IDUC

calculo_leituras_ucb.iduc calculo_data.iduc

Follows below code.


begin

 execute immediate 'alter table calculo_leituras_ucb disable all triggers';
 execute immediate 'alter table calculo_dados disable all triggers';

UPDATE calculo_leituras_ucb
SET calculo_leituras_ucb.idnaoleitura = 0
from calculo_leituras_ucb 
inner join calculo_dados on  calculo_leituras_ucb.iduc = calculo_dados.iduc 
where calculo_leituras_ucb.ano_mes = ('01/07/2014')
and calculo_dados.idrota in (35,45,48,53,60,68,70,79)
and calculo_leituras_ucb.idnaoleitura = 24 ;


 commit;

 execute immediate 'alter table calculo_leituras_ucb enable all triggers';
 execute immediate 'alter table calculo_dados enable all triggers';

    exception when others then
        rollback;

        execute immediate 'alter table Calculo_leituras_ucb enable all triggers';
         execute immediate 'alter table calculo_dados enable all triggers';

        RAISE_APPLICATION_ERROR(-20000,'tem erro no script >:( !!!.'|| sqlerrm(sqlcode));

end;
database oracle pl-sql

5 answers

5


As far as I know there’s no Oracle, but you can do whatever you want with exists:

UPDATE calculo_leituras_ucb
SET calculo_leituras_ucb.idnaoleitura = 0
where exists (
    select calculo_dados.iduc 
    from calculo_dados
    where calculo_dados.idrota in (35,45,48,53,60,68,70,79)
    and calculo_leituras_ucb.iduc = calculo_dados.iduc
)
and calculo_leituras_ucb.ano_mes = ('01/07/2014')
and calculo_leituras_ucb.idnaoleitura = 24 ;

3

Can also be done with a sub-query in:

UPDATE calculo_leituras_ucb clu
   SET clu.idnaoleitura = 0
 WHERE clu.ano_mes = ('01/07/2014')
   AND clu.idnaoleitura = 24
   AND clu.iduc IN (SELECT cd.iduc
                      FROM calculo_dados cd
                     WHERE cd.idrota IN (35, 45, 48, 53, 60, 68, 70, 79));

3

As far as I know it doesn’t, one solution is to mount a cursor

...
FOR R IN (SELECT calculo_leituras_ucb.iduc
          from calculo_leituras_ucb 
          inner join calculo_dados on  calculo_leituras_ucb.iduc = calculo_dados.iduc 
          where calculo_leituras_ucb.ano_mes = ('01/07/2014')
          and CALCULO_DADOS.IDROTA in (35,45,48,53,60,68,70,79)
          and CALCULO_LEITURAS_UCB.IDNAOLEITURA = 24)
LOOP
  update CALCULO_LEITURAS_UCB
  set CALCULO_LEITURAS_UCB.IDNAOLEITURA = 0
  WHERE iduc = R.iduc;
end LOOP;
...

2

UPDATE
(SELECT calculo_leituras_ucb.iduc
          from calculo_leituras_ucb 
          inner join calculo_dados on  calculo_leituras_ucb.iduc = calculo_dados.iduc 
          where calculo_leituras_ucb.ano_mes = ('01/07/2014')
          and CALCULO_DADOS.IDROTA in (35,45,48,53,60,68,70,79)
          and CALCULO_LEITURAS_UCB.IDNAOLEITURA = 24) DADOS
 SET DADOS.IDNAOLEITURA = 0

1

UPDATE 
(SELECT tabela_1.valor AS valor_OLD, tabela_2.valor as valor_novo
 FROM tabela_1
 INNER JOIN tabela_2
 ON tabela_1.codigo= tabela_2.codigo
) tb
SET tb.valor_OLD= tb.valor_novo

Browser other questions tagged database oracle pl-sql

You are not signed in. Login or sign up in order to post.