Home » SQL & PL/SQL » SQL & PL/SQL » bulk date update add months (merged) (pl/sql)
bulk date update add months (merged) [message #686597] Thu, 20 October 2022 13:02 Go to next message
rhnshk
Messages: 26
Registered: May 2008
Junior Member
hi
i want to update a table from the back-end, which has the below data (example of 1 record, RETURNING 6 ROWS, based on dc_lease_no);
SQL>  ed
Wrote file afiedt.buf

  1  select a.dc_lease_no,
  2  b.DC_LEASE_FROM_PERIOD,
  3  add_months(b.DC_LEASE_FROM_PERIOD,1) DC_PDC_RENT_FROM,
  4  add_months(b.DC_LEASE_FROM_PERIOD,1)-1 DC_PDC_RENT_UPTO
  5  from dc_lease_pdc_chq a, dc_lease_txn b
  6  where a.dc_pdc_lease_no = b.DC_LEASE_NO
  7* and a.dc_lease_no = 10187
SQL> /

DC_PDC_LEASE_NO DC_LEASE_F DC_PDC_REN DC_PDC_REN
--------------- ---------- ---------- ----------
      10187.000 01/10/2022 01/11/2022 31/10/2022
      10187.000 01/10/2022 01/11/2022 31/10/2022
      10187.000 01/10/2022 01/11/2022 31/10/2022
      10187.000 01/10/2022 01/11/2022 31/10/2022
      10187.000 01/10/2022 01/11/2022 31/10/2022
      10187.000 01/10/2022 01/11/2022 31/10/2022

6 rows selected.

i want to update the table [u]dc_lease_pdc_chq[/u] by updating its DC_PDC_RENT_FROM & DC_PDC_RENT_UPTO columns,
for the all the lease numbers in [u]dc_lease_txn[/u] table. 
[i]count of each lease_no wise records in dc_lease_pdc_chq table differs.[/i]

after the update, the records for lease_no 10187 should look like the below result.

DC_PDC_LEASE_NO DC_LEASE_F DC_PDC_REN DC_PDC_REN
--------------- ---------- ---------- ----------
      10187.000 01/10/2022 01/10/2022 31/10/2022
      10187.000 01/10/2022 01/11/2022 30/11/2022
      10187.000 01/10/2022 01/12/2022 31/12/2022
      10187.000 01/10/2022 01/01/2023 31/01/2023
      10187.000 01/10/2022 01/02/2023 28/02/2023
      10187.000 01/10/2022 01/03/2023 31/03/2023
i wonder whether if its acheivable with the update statement?


Re: bulk date update add months (merged) [message #686607 is a reply to message #686597] Fri, 21 October 2022 14:31 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
merge
  into dc_lease_pdc_chq t
  using (
         select  a.rowid rid,
                 add_months(b.dc_lease_from_period,rownum)         dc_pdc_rent_from,
                 add_months(b.dc_lease_from_period,rownum + 1) - 1 dc_pdc_rent_upto
           from  dc_lease_pdc_chq a,
                 dc_lease_txn b
           where a.dc_pdc_lease_no = b.dc_lease_no
             and b.dc_lease_no = 10187
        ) s
  on (
      t.rowid = s.rid
     )
  when matched
    then
      update
         set t.dc_pdc_rent_from = s.dc_pdc_rent_from,
             t.dc_pdc_rent_upto = s.dc_pdc_rent_upto - 1
/

SY.
Previous Topic: Dynamic Source Table & Target Table
Next Topic: ORA-29005: The certificate is invalid
Goto Forum:
  


Current Time: Thu Mar 28 20:21:38 CDT 2024