Thanks Svein, worked perfectly
Jack


--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> 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.