bulk date update add months (merged) [message #686597] |
Thu, 20 October 2022 13:02 |
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 |
Solomon Yakobson
Messages: 3275 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.
|
|
|