Set value of a column based on another column-CodePudding

I have the following table in Postgres 11.

col1    col2  source    col3
a       abc   curation  rejected
a       abc   DB 
b       etg   DB        accepted
c       jfh   curation

How can I assign value in col3 based on the values in col1

The expected output is:

col1    col2  source    col3
a       abc   curation  rejected
a       abc   DB        rejected
b       etg   DB        accepted
c       jfh   curation  null

Is there a way to check if values in col1 and col2 in subsequent rows are identical, then assign same col3 to all the rows (if the col3 value in other row is null).

Any help is highly appreciated.

CodePudding user response：

You're not entirely clear on what the criteria is, but at a basic level it could depend on how you want to query this data, there are multiple ways you could do this.

Generated Columns

drop table if exists atable ; 

CREATE TABLE atable (
 cola text , 
 colb text GENERATED ALWAYS AS (case when cola='a' then 'rejected' else 
null end) STORED
);

insert into atable(cola) values ('a')

A View.

create or replace view aview as 
select cola, case when cola='a' then 'rejected' else null end as colb 
from atable;

Both would yield the same results.

cola|colb    |
---- -------- 
a   |rejected|

Other options could be a materialized view, simple query logic.

You have options.

CodePudding user response：

update a2 set
                col3 =
                  case when col1 = 'a' then 'rejected'
                  when col1 = 'b' then 'accepted'
                  when col1 = 'c' then 'null' end
where col3 is  null
returning *;

You can also set triggers. But generated columns only available from 12. So you need upgrade to use generated columns.
db fiddle

CodePudding user response：

You can try to use MAX window function.

SELECT col1,col2,MAX(col3) OVER(PARTITION BY col1,col2) col3
FROM T

if you want to UPDATE value you can try to UPDATE by a subquery

UPDATE T t1
SET col3 = c.new_col3
FROM ( 
    SELECT col1,col2,MAX(col3) OVER(PARTITION BY col1,col2) new_col3
    FROM T 
) c
WHERE t1.col1 = c.col1 
AND t1.col2 = c.col2;

SELECT * FROM T;

col1 | col2 | col3    
:--- | :--- | :-------
a    | abc  | rejected
a    | abc  | rejected
b    | etg  | accepted
c    | jfh  | null

db<>fiddle here