How to do an update with inner join in Oracle

Question

How to do an update with inner join in Oracle

Navigation

#1 by (5 votes)
#2 by (3 votes)
#3 by (3 votes)
#4 by (1 votes)

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;

oracle pl-sql

asked by anonymous 15.07.2014 / 18:07

4 answers

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

Conditions in Javascript Count equal records with SELECT DISTINCT and COUNT

score 5 · Accepted Answer

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 ;