Get overllaped data from two tables with same structure, giving prefrence to other : Oracle
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 ?
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().