Joining to same table and updating column on row match for multiple columns

Question

I have a single table (table_1) of schema below:

row_id identifier col1 col2 col3 col4 status
1 A 1 2 3 4 
2 A 2 3 4 5 
3 B 1 2 3 4
4 B 2 3 4 6
5 C 1 2 3 4

I want to join on identifier = A such that any rows with identifier != A but which match on values for col1, col2, col3 and col4 will update the status column with values for any matched ('B,C'), and another for mismatch (foo).

Note that the status column values need to update with the identifier(s) matched.

row_id identifier col1 col2 col3 col4 status
1 A 1 2 3 4 B,C
2 A 2 3 4 5 foo
3 B 1 2 3 4
4 B 2 3 4 6
5 C 1 2 3 4

I've used a single SELECT statement with multiple subqueries for each separate column to match to on 'A', but had to explicitly select other identifiers and resulted in errors.

Corrected row_id to show the primary key.

Answer 1

You can do it using a self-join and an aggregate function (string_agg) to aggregate the identifiers that are different from the current row as below.

The first and last lines initializes the status column to NULL and finalizes it to foo if it isn't assigned, respectively:

UPDATE table_1 t1 SET status = NULL;
UPDATE table_1 t1
SET status = (
 SELECT string_agg(t2.identifier,',') FROM table_1 t2
 WHERE t2.identifier != t1.identifier
 AND t1.col1 = t2.col1
 AND t1.col2 = t2.col2
 AND t1.col3 = t2.col3
 AND t1.col4 = t2.col4
);
UPDATE table_1 t1 SET status = 'foo' WHERE status IS NULL;

Answer 2

update test_table t1
inner join (
 select *, 
 (case 
 when status_value is null then 'foo'
 else status_value
 end) as to_update_status from
 (select *, group_concat(case
 when identifier='A' then null
 else identifier 
 end
 ) as status_value from test_table
 group by col1, col2, col3, col4) as t1 where identifier='A' 
 ) t2 on t1.row_id = t2.row_id 
 and t1.identifier=t2.identifier
 and t1.col1 = t2.col1
 and t1.col2 = t2.col2
 and t1.col3 = t2.col3
 and t1.col4 = t2.col4
 set t1.status = t2.to_update_status;

In the above query, test_table is the name of the table.

Consider changing the structure of your table. It should have a unique identifier.(From the name, the column row_id should be unique, but it is not in the data you provided, so I had to put 5 additional checks in the join query). If your row has a unique identifier, you could select just the row_id and to_update_status in the subquery and simply check the row_id

You should also have an index on identifer column and a multi-column index on col1, col2, col3, col4.