我使用的是SQL开发人员,希望使用一个唯一的ID和不同的行ID用不同的vlue更新不同的行。例如,如下所述:
-
更新repayment_tschedule set from date='03-SEP-22',duedate='03-OCT-22',其中'22'中的loan_id和'2'中的分期付款
更新m_loan_repayment_schedule set from date='03-OCT-22',duedate='03-NOV-22',其中'22'中的loan_id和'3'中的分期付款
更新repayment _ schedule set from date='03-NOV-22',duedate='03-DEC-22',其中'22'中的loan_id和'4'中的分期付款
此处Loan_id是所有行中唯一的值。
如何仅使用单个查询来更新具有不同值的行。
使用MERGE
语句:
MERGE INTO repayment_schedule dst
USING (
SELECT DATE '2022-09-03' AS fromdate, DATE '2022-10-03' AS todate, 22 AS loan_id, 2 AS installment FROM DUAL UNION ALL
SELECT DATE '2022-10-03', DATE '2022-11-03', 22, 3 FROM DUAL UNION ALL
SELECT DATE '2022-11-03', DATE '2022-12-03', 22, 4 FROM DUAL
) src
ON (dst.loan_id = src.loan_id AND dst.installment = src.installment)
WHEN MATCHED THEN
UPDATE
SET fromdate = src.fromdate,
todate = src.todate;
然后,对于样本数据:
CREATE TABLE repayment_schedule (fromdate, todate, loan_id, installment) AS
SELECT DATE '2022-08-03', DATE '2022-09-03', 22, LEVEL FROM DUAL CONNECT BY LEVEL <= 4;
MERGE
之后,表格将包含:
FROMDATE 2022-08-03 00:00:00 22 1 2022-09-03 00:00:00 2022-10-03 00:00:00 2022-11-03 00:00:00