CODEWITHSUNDEEP
sqloracle-database
user3769778
user3769778

Reputation: 967

Get overlapped 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.

enter image description here

I have this two tables: MY_DATA and MY_DATA_CHANGE in my Oracle database. 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:

enter image description here

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 ?

Upvotes: 0

Views: 35

Answers (1)

Matt
Matt

Reputation: 14361

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().

Upvotes: 1

Related Questions