Home / Questions / 289167
Accepted
Ivxn
Asked: 2020-08-30 10:12:39 +0800 CST 2020-08-30 10:12:39 +0800 CST

Update through inner join in oracle

  • 772

How can I pass this queryto Oracle?, I'm updating a field from table A through inner jointable B:

UPDATE ATA
SET A.ID_IN = B.id_in
FROM TMP_TRF_ALL ATA
INNER JOIN items B
on ATA.seq=B.seq
WHERE ATA.PROCESADO = 1
AND  ATA.EXT = '213'
AND B.ID_COMPANY='1'

I have done this, but it is not correct because it does not update and it is as if I wanted to update the field ID_INof the table items, when in fact I want to update the field ID_INof the tableTMP_TRF_ALL 

UPDATE 
(SELECT ATA.id_in as T1IV,B.id_in AS T2IV FROM TMP_TRF_ALL ATA  INNER JOIN items B 
on ATA.seq=B.seq 
WHERE ATA.PROCESADO = 1 AND  ATA.EXT = '213' AND B.ID_COMPANY='1')T
SET T.T1IV=T.T2IV
oracle

1 Answers

  1. Best Answer

    You can use a statement mergeto achieve the goal. In your case, it would be something like:

    merge into TMP_TRF_ALL ATA
using (select * from items where ID_COMPANY = '1') B
   on (    ATA.seq = B.seq
       and ATA.Procesado = 1
       and ATA.EXT = '213'
      )
when matched then 
update
   set id_in = B.id_in;
    • 3