unique constraint violation

[benjamenus]

I have 2 tables:

t1 (100 rows) has columns a,b,c (combined a,b,c make each row unique)
t2 (5 rows) has columns a,b,c,d (a,b,c or a,b,d make each row unique)

t2 (a,b,c) was created from t1 (a,b,c) so linking on these three fields means that there can only be one matching row in t1 for every row in t2.

The update (obviously not written like this) is basically:

update t1.c with t2.d
where t1.a=t2.a
and t1.b=t2.b
and t1.c=t2.c

From this I would expect 5 rows to be updated in t1. However, this violates the unique constraint in t1.

What I need to do is identify which row(s) / value(s) are causing this but cannot seem to get the right query.

 

[jquaid]

I would have thought that a simple select like the following would return the t1/t2 rows which violate the unique constraint:

SELECT t1.a, t1.b, t1.c
FROM t1, t2
WHERE t1.a = t2.a
AND t1.b = t2.b
AND t1.c = t2.d;

Regards,
Jeremy

 

[benjamenus]

Cheers Jeremy

Unfortunately that won't work. I probably didn't expain it very well, but it is possible that t2.d is the same as t2.c (and therefore t1.c) - basically updating the record with identical data which, although inefficient, is fine if t1.c was OK to begin with. The query you suggest would return all of these rows as well as those which cause the error.

As it happens, this is the query that gives the results needed:

select t1.*
from t1,t2
where t1.a=t2.a
and t1.b=t2.b
and t1.c=t2.d
and t2.c<>t2.d;

Thanks for your help