Oracle Merge row by row

Question

Sorry if i don't format everything correctly, it's my first time posting here, and sorry for bad english.

I have a big query that returns something like this :

col1 | col2 |col3|col4
==========================
A    |A1    |A2  |01/01/20
A    |A1    |A3  |01/02/20
A    |' '   |' ' |20/02/20
B    |' '   |' ' |01/01/20
C    |C1    |C2  |01/01/20
C    |C1    |C3  |01/02/20

And i want to insert this result into a table like this :

col1 | col2 |col3|col4
==========================
A    |A1    |A3  |01/02/20
B    |' '   |' ' |01/01/20
C    |C1    |C3  |01/02/20

I tried something like:

MERGE INTO dest_table d
USING (SELECT ....) a 
on a.col1 = d.col1
WHEN NOT MATCHED THEN INSERT
WHEN MATCHED THEN IF col2 = ' ' THEN do nothing ELSE compare date and keep the most recent one

When i do this, it insert every rows

How i want it to work is like :

Insert "A;A1;A2" because NOT MATCHED
"A;A1;A3" IS MATCHED, because "A" was just inserted previously, so enter the "MATCHED" case
"A;' ';' '" same as the previous one

Can i do something like that with "MERGE INTO" or should i try another method ?

Do you want to insert the last version of the key col1 based on the date in col4? If so use something like rownum() over (partition by col1 order by col4 desc) = 1 for filter. — Marmite Bomber

Akina Akina · Accepted Answer · 2020-02-06T17:13:29

SELECT DISTINCT col1,
                FIRST_VALUE(col2) OVER (PARTITION BY col1 
                                        ORDER BY CASE WHEN col2 = '  ' 
                                                      THEN DATE '1980-01-01'
                                                      ELSE col4
                                                      END DESC) col2,
                FIRST_VALUE(col3) OVER (PARTITION BY col1 
                                        ORDER BY CASE WHEN col2 = '  ' 
                                                      THEN DATE '1980-01-01'
                                                      ELSE col4
                                                      END DESC) col3,
                FIRST_VALUE(col4) OVER (PARTITION BY col1 
                                        ORDER BY CASE WHEN col2 = '  ' 
                                                      THEN DATE '1980-01-01'
                                                      ELSE col4
                                                      END DESC) col4
FROM test
ORDER BY col1;

fiddle

Oracle Merge row by row

1 Answers