Joining to same table and updating column on row match for multiple columns 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.
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;