UPDATE command not executing resulting in Primary Key Error

[former_SQLA_member1694895]

Hi,

I have the following statement that I am running;

    declare @prod varchar(25)
    set @prod = (select top 1 prodref
            from dba.proditem
            where prodcateg = '84' 
            AND obsolete = 0
            AND defaultsupersededby is null
            AND charindex('/B/',proddesc) > 0
            AND salesanalysis is null 
            order by prodref asc)
    declare @cnt INT
    set @cnt = 1
    while @cnt <= (select count(prodref)
            from dba.proditem
            where prodcateg = '84' 
            AND obsolete = 0
            AND defaultsupersededby is null
            AND charindex('/B/',proddesc) > 0)
    BEGIN
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601110GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601115GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601120GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601130GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601140GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601150GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601160GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity, ratioquantity,linktype)
      VALUES ('C',@prod,'601170GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601179GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'601180GST',100,1,3)
      INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                                    defaultquantity,ratioquantity,linktype)
      VALUES ('C',@prod,'600965GST',100,1,3)
      UPDATE dba.proditem
         SET salesanalysis = 'B'
       WHERE prodref = @prod
      set @cnt = @cnt+1
    END

The statement appears to run through once fine, then on the second time it errors telling me the 'Primary Key' in proditemlinks is not unique. The primary key for proditemlinks is 'opco_code','prodref','linked_prodref'.

What appears to be happening, is the statements runs through once fine. On the second run the lines are inserted but the update statement at the end is not run, so it tries to insert the last item again, as it is not filtered out on the select statement.

[MarkCulp]

A primary key value must be unique. During the second pass of the loop you are trying to insert the same values into the table and hence the primary key values (of the insert rows) are no longer unique.

E.g. During the first the first insert statement is

INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,
                defaultquantity, ratioquantity, linktype )
VALUES ('C',@prod,'601110GST',100,1,3)

so it inserts a row with

* opco_code = 'C'
* prodref = @prod
* linked_prodref = '601110GST'
* ... etc.

and this same value is being inserted during the second pass. You say these three fields comprise the primary key on the table ... so your code is trying to insert the same primary key tuple during the second pass, hence the 'Primary key not unique' error.

HTH

[former_SQLA_member1694895]

Hi Mark

Thanks for the reply.

I understand the purpose of the Primary Key but what I don't understand is why it is trying to insert the same data.

After the insert statement is run, a 'B' should be assigned to the product so it is then filtered out on the next loop. This works fine on the first run. The second run inserts the correct data into proditemlinks, but fails to update the salesanalysis field which is why there is a duplicate issue.

I am trying to understand why the salesanlysis field is not getting updated.

[MarkCulp]

Your update statement at the bottom of the loop does not change the proditemlinks table so all rows that you have inserted will not have changed. Your selection of @prod is outside of the loop so it does not change... so nothing has changed, and hence the same values are being inserted on the second pass through your while loop.

[former_SQLA_member1694895]

OK, my understanding was the update statement will update the proditem table which is then queried for the next line as I am using top 1.

So if I was to start this from scratch, the first @prod to be inserted would be 8400001, this inserts the lines into proditemlinks and then updates the proditem.salesanalysis field with a 'B' which then filters this out of my select statement on the second run. The second item number is 8400005, it inserts the lines and then produces the error.

So I can't see what you are suggesting is right.