CODEWITHSUNDEEP
sqlpostgresql
Andrii Plotnikov
Andrii Plotnikov

Reputation: 3372

SQL select *, but override 'column value' of row if present in another table

For simplicity let's say I have two tables with identical rows.

User with fields "id" and "name". And user_override with fields "id" and "name"

I want to run Select statement that will select all values from User and override any value if it is present in UserOverride

something like (obviously fails - COALESCE does not take au.* as argument)

SELECT COALESCE(uo.*, u.*) FROM USER u LEFT JOIN USER_OVERRIDE uo ON u.id = uo.id

and result would be something like

id | name
1  | 'normal name 1'
2  | 'overriden name 2'
3  | 'normal name 3

I would do it with simply overriding each value but it's going to be in function and I'd rather not change it every single time and just reuse the same query.

It is guaranteed that those two tables have same columns.

Is something like this possible? What would be the syntax?

Upvotes: 3

Views: 6417

Answers (3)

user10679624
user10679624

Reputation:

you were close to the answer:

SELECT  u.id ,COALESCE(uo.Name, u.name) as name_New
FROM USER u 
LEFT JOIN USER_OVERRIDE uo ON u.id = uo.id

you just can't coalesce everything with one instruction.

That's because Coalesce ( check ref:coalesce)

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL

So you have to evaluate every single column separately

Upvotes: 4

forpas
forpas

Reputation: 164174

You need to select from both tables with different conditions and merge with UNION:

SELECT u.id, u.name
FROM user u
WHERE NOT EXISTS (SELECT 1 FROM user u WHERE u.id = uo.id)
UNION 
SELECT uo.id, uo.name
FROM user_override uo
WHERE EXISTS (SELECT 1 FROM user u WHERE u.id = uo.id)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270693

Expressions only return a single value. You can do:

select u.id, coalesce(uo.name, u.name) as name
from user u left join
     user_override uo
     on uo.id = u.id;

If all values are being overridden in any given row, you can do:

select uo.id, uo.name
from user_override uo
union all
select u.id, u.name
from user u
where not exists (select 1 from user u where u.id = uo.id);

Upvotes: 0

Related Questions