Hi Jack!

try something like this:

INSERT INTO PTST(UOP_NO, STATE_NO, TEST_NO, ABS_NO)
SELECT DISTINCT UOP_NO, STATE_NO, 0, -2
FROM PTST p1
where not exists(
SELECT *
FROM PTST p2
where p2.UOP_NO = p1.UOP_NO
and p2.STATE_NO = p1.STATE_NO
and p2.TEST_NO = 0)

HTH,
Set

--- In firebird-support@yahoogroups.com, "jackmills75" wrote:
>
> Hi
> Table PTST has PK based on 3 columns UOP_NO, STATE_NO, TEST_NO, all
> integer.
>
> Rows get inserted e.g. with UOP_NO = 1, STATE_NO = 0, TEST_NO = 1
> plus other column data but initially not with test_no = 0, I need to
> add a new row setting TEST_NO = 0 with the existing UOP_NO &
STATE_NO
>
> This query runs once ok & then bombs out because of violation of
> primary key (which I understand)
>
> INSERT INTO PTST(UOP_NO, STATE_NO, TEST_NO, ABS_NO)
> SELECT DISTINCT UOP_NO, STATE_NO, 0, -2
> FROM PTST
>
> Is there any way it can be modified so that once a row has
> TEST_NO = 0 that there is no insert for that PK combination.
>
> Hope that makes sense.