1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| MERGE INTO TEST_DEST tgt
USING (
with SR (CODE1, CODE2, CODE3, CODE4, CODE5, CODE6, DATE, QTE1, QTE2, rn) as
(
select CODE1, CODE2, CODE3, CODE4, CODE5, CODE6, DATE, QTE1, QTE2
, row_number() over(partition by CODE1, CODE2, CODE3, CODE4, CODE5, CODE6, date order by date desc)
from TEST_TEMP
)
select CODE1, CODE2, CODE3, CODE4, CODE5, CODE6, DATE, QTE1, QTE2
from SR
where rn = 1
) src
ON (tgt.CODE1 = src.CODE1
AND tgt.CODE2 = src.CODE2
AND tgt.CODE3 = src.CODE3
AND tgt.CODE4 = src.CODE4
AND tgt.CODE5 = src.CODE5
AND tgt.CODE6 = src.CODE6
AND tgt.DATE = src.DATE)
WHEN MATCHED THEN UPDATE
SET tgt.QTE1 = src.QTE1
, tgt.QTE2 = src.QTE2
WHEN NOT MATCHED THEN INSERT
(tgt.CODE1, tgt.CODE2, tgt.CODE3, tgt.CODE4, tgt.CODE5, tgt.CODE6, tgt.DATE, tgt.QTE1, tgt.QTE2)
VALUES (src.CODE1, src.CODE2, src.CODE3, src.CODE4, src.CODE5, src.CODE6, src.DATE, src.QTE1, src.QTE2); |
Partager