How to join a base table with update table with change data?

userCxr1 0
Viewed 34

I have two tables (in real code there is more data columns, this is just an example with single data column)

create table base_t (
    id int primary key,
    val int;
);
create table update_t (
    id int primary key,
    c char (1),
    val int;
);

In update table, char column can be 'A' (add), 'D' (delete) or 'U' (update). In the result view I need to get rows from update_t when c is 'A' or 'U', when c is 'D' that row should not be present in result and all other rows should be taken from base_t table.

Example contents of base_t table

1 10
2 20
3 30

contents of update_t table

2 'U' 21
3 'D' NULL
4 'A' 41

expected result

1 10
2 21
4 41

How to create such join in postgresql?

sql postgresql
1 Answers

You could use COALESCE() for the U rows, preferring the update_t table over the base_t table. This is assuming that no A records in update_t would exist yet in base_t. Otherwise you could change that COALESCE() over to a case expression like CASE WHEN update_t.c = 'UPDATE' then update_t.id ELSE base_t.id END as id (and similar for val) column.

SELECT COALESCE(update_t.id, base_t.id) as id,
    COALESCE(update_t.val, base_t.val) as val
FROM base_t
    FULL OUTER JOIN update_t 
         ON base_t.id = update_t.id
WHERE 
    update_t.c <> 'D'
JNevill0
Related