How to do an update with inner join in Oracle

2

Good afternoon,

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

The calculating_table_count table has the field idnaoleitura The data_calculation table has the idrota field The keys are the fields: IDUC

calculo_leituras_ucb.iduc calculo_dados.iduc

Follow the code below.


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; 


    
asked by anonymous 15.07.2014 / 18:07

4 answers

5

As far as I know, there's no way for Oracle, but you can do what 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 ;
    
15.07.2014 / 18:58
3

As far as you know does not, a 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;
...
    
15.07.2014 / 18:23
3

It can also be done with a sub-query in 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));
    
10.03.2015 / 18:48
1
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
    
28.12.2016 / 19:46