Get overllaped data from two tables with same structure, giving prefrence to other : Oracle

Question

I am completely lost thinking about how do I solve this challenge of data retrieving.

I have this two tables: MY_DATA and MY_DATA_CHANGE in my Oracle DB.
I wanted to select data some thing like this:

SELECT ALL COLUMNS FROM MY_DATA WHERE ID IN (1,2,4,5) FROM MY_DATA BUT IF ANY ID IS PRESENT IN (1,2,4,5) IN MY_DATA_CHANGE THEN USE ROW FROM MY_DATA_CHANGE

So my overall result must look like:

I can only use SQL not stored procedure, as this query is going to be part of another very big query (legacy code written long back) (will be used in Crystal reports tool to create report).

So guys please help. My column data contains CLOB and the usual UNION logic does not work on them.

How do I do it ?

Answer 1

SELECT
    m.Id
    ,COALESCE(c.CLOB1,m.CLOB1) as CLOB1
    ,COALESCE(c.CLOB2,m.CLOB2) as CLOB2
FROM
    MY_DATA m
    LEFT JOIN MY_DATA_CHANGE c
    ON m.Id = c.Id
WHERE 
    m.ID IN (1,2,4,5)

The way I would choose to do that is via a LEFT JOIN between the two tables and then use COALESCE().

Get overllaped data from two tables with same structure, giving prefrence to other : Oracle

Sign up