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.

asked 26 May '16, 10:01

Jongee's gravatar image

Jongee
217171722
accept rate: 0%

edited 26 May '16, 11:24

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297


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

permanent link

answered 26 May '16, 10:53

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

edited 26 May '16, 10:54

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.

(26 May '16, 10:58) Jongee
Replies hidden

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.

(26 May '16, 11:03) Mark Culp

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.

(26 May '16, 11:35) Jongee
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×5

question asked: 26 May '16, 10:01

question was seen: 1,711 times

last updated: 26 May '16, 11:35