I have 2 different tables with same data structure, table A and B, is it possible to get values from A and then B where if the ID entry exists in B it replaces the value got in A?
Example:
select '1' as id, 'Bob' as "user" from dual
union
select '1' as id, 'Alice' as "user" from dual
This returns:
1 Bob
1 Alice
If id
is the same in the second select I would like to have only one row:
1 Alice
CodePudding user response:
Rather than a UNION, you need a "FULL OUTER JOIN", which will give you three types of row:
- Rows where both tables have a matching id, with values from both tables (the rows which an INNER JOIN would return)
- Rows where only table A has that id, with null for table B's columns (the additional rows which a LEFT OUTER JOIN would return)
- Rows where only table B has that id, with null for table A's columns (the additional rows which a RIGHT OUTER JOIN would return)
You can then use COALESCE to take the values from B if present (the first and second type of row), and A if not (the third type of row).
So for your example:
Select
Coalesce(B.id, A.id) as id,
Coalesce(B."user", A."user") as "user"
From
(select '1' as id, 'Bob' as "user" from dual) as A
Full Outer Join
(select '1' as id, 'Alice' as "user" from dual) as B
On B.id = A.id
Which returns:
id | user |
---|---|
1 | Alice |
(Note: tested on SQL Server, by removing the "from dual", because I have no Oracle DB to test on.)
CodePudding user response:
One way to do it is:
select '1' as id, 'Bob' as "user" from table_A
where id not in (select id from table_B)
union all
select '1' as id, 'Alice' as "user" from table_B