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      
2           B            1       2       3       4
2           B            2       3       4       6
2           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
2           B            1       2       3       4
2           B            2       3       4       6
2           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.

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.

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      
2           B            1       2       3       4
2           B            2       3       4       6
2           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
2           B            1       2       3       4
2           B            2       3       4       6
2           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.

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      
2           B            1       2       3       4
2           B            2       3       4       6
2           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
2           B            1       2       3       4
2           B            2       3       4       6
2           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.