Update table column based on join in Oracle

Question

I have 2 tables like below:

select vsat_detail_id, sap_id, AZIMUTH_GSAT_16 from tbl_vsat_mst_detail_2409 where vsat_detail_id = 104; Table 1

select vsat_id, sap_id, azimuth_60_e from tbl_vsat_mst_detail where vsat_id = 104; Table 2

So I am updating like below:-

MERGE
 INTO    tbl_vsat_mst_detail trg
USING   (
    SELECT  t1.AZIMUTH_GSAT_16 AS rid, t2.azimuth_60_e as code
    FROM    tbl_vsat_mst_detail_2409 t1
    JOIN    tbl_vsat_mst_detail t2
    ON      t1.vsat_detail_id = t2.vsat_id
    WHERE   t1.vsat_detail_id = 104
    ) src
ON      (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
SET trg.value = code;

but nothing is getting updated. Please help

Answer 1

You are comparing the ROWID pseudo-column for the tbl_vsat_mst_detail table to the AZIMUTH_GSAT_16 column of the tbl_vsat_mst_detail_2409 table. Those two are unlikely to be equal so it is unsurprising that they do not match.

You probably want something like:

MERGE INTO tbl_vsat_mst_detail trg
USING      tbl_vsat_mst_detail_2409 src
ON  (   src.vsat_detail_id = trg.vsat_id
    AND src.vsat_detail_id = 104)
WHEN MATCHED THEN
  UPDATE
  SET trg.azimuth_60_e = src.AZIMUTH_GSAT_16;