How to find the record that violate unique key constraint?

Question

Assume I have two tables as follows,

Table A

    +-------+-------+-------+-------+
    | col_a | col_b | col_c | col_d |
    +-------+-------+-------+-------+
    |       |       |       |       |
    +-------+-------+-------+-------+

Table B

    +-------+-------+-------+-------+
    | col_a | col_b | col_c | col_d |
    +-------+-------+-------+-------+
    |       |       |       |       |
    +-------+-------+-------+-------+

I'm going to update Table A using Table B. Here are the conditions

1. records that are equal by col_a should update in Table A
2. records are not equal by col_a should inserted to Table A
3. Table A has a unique key constraint as (col_b,col_c,col_d)

Problem is when updating data in Table A, this unique key constraint fails for some records. Question is how can I identify records that violate unique key constraint using a query. (I don't have access to logs)

Answer 1

If you don't have a unique key on col_b, col_c, col_d of table_b, this will result in a violation when copying over. You can identify problematic rows with a query like this:

SELECT col_b, col_c, col_d
  FROM table_b
  GROUP BY col_b, col_c, col_d
  HAVING COUNT(*) > 1

A similar query can be run on table_a joined to table_b, but the specific queries to run will depend on which columns will be updated in table_a. For the insert case, a useful technique might be to use a MINUS between table_a and the proposed inserted row.

Answer 2

If I correctly understand your need, maybe something like this can find the rows that will give problems:

select *
from table_a a
inner join table_b b 
on (a.col_b  = b.col_b and
    a.col_c  = b.col_c and
    a.col_d  = b.col_d and
    a.col_a != b.col_a
   )