Update Data from another table with more conditions PL/SQL-CodePudding

So I have this following table Table 1

NIP	NAMA_PENSIUN	JNS_KELAMIN	ID_PTKP	NPWP_PENSIUN
195605212010002	Hardjadi	Laki-Laki	K3	939766245522000
195402192010003	Sutikno	Laki-Laki	K1	937896346533000
196008142010004	Adlina Humaira	Perempuan	TK0	937686259522000
196401012010005	Retno Subandi	Laki-Laki	TK2	917678275532000
195908302010006	Baby Fajrina	Perempuan	K3	982638279888000

Table 2

ID_PTKP	KET_PTKP	TARIF_PTKP
TK0	Tidak Kawin - 0 Tanggungan	54,000,000
TK1	Tidak Kawin - 1 Tanggungan	58,500,000
TK2	Tidak Kawin - 2 Tanggungan	63,000,000
TK3	Tidak Kawin - 3 Tanggungan	67,500,000

Table 3

TAHUN_PAJAK	NPWP_PENSIUN	PENGHSL_PENSIUN
2021	939766245522000	7500000
2021	937896346533000	4500000
2021	937686259522000	4000000

I want to update NILAI_PTKP in the table 3 from TARIF_PTKP table 2, but table 3 has the same identifier which is NPWP_PENSIUN with table 1, and table 1 has the same identifier from table 2 which is ID_PTKP. from this what syntax I should use?

CodePudding user response：

merge is one option:

SQL> merge into table3 c
  2    using (select a.npwp_pensiun, b.tarif_ptkp
  3           from table1 a join table2 b on a.id_ptkp = b.id_ptkp
  4          ) x
  5    on (c.npwp_pensiun = x.npwp_pensiun)
  6    when matched then update set
  7      c.nilai_ptkp = x.tarif_ptkp;

1 row merged.

SQL> select * From table3;

      NPWP_PENSIUN NILAI_PTKP
------------------ ----------
   939766245522000
   937896346533000
   937686259522000   54000000

SQL>

Why is only one row updated? Because sample data you posted doesn't have match between table1 and table2.

CodePudding user response：

Already solve with

UPDATE (SELECT pt.NILAI_PTKP A, tp.TARIF_PTKP B FROM PajakTahunan pt  
JOIN MasterPensiun mp ON pt.NPWP_PENSIUN = mp.NPWP_PENSIUN
JOIN TarifPTKP tp ON mp.ID_PTKP = tp.ID_PTKP)
SET A = B;