T-SQL help

running:

update iv00112
set bin = 'SERVICE'
where locncode = '106'
and bin = 'recloser'

produces:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKIV00112'. Cannot insert duplicate key in object 'dbo.iv00112'.
The statement has been terminated.

running:

update iv00112
set bin = 'SERVICE'
where locncode = '106'
and itemnmbr = '10003'

this works, note that itemnmbr 10003 was in the Bin "recloser".

why can i change the bin location when i do it by itemnmbr, but not when i try to do it by "recloser". there are 100s of entries with recloser as the bin.
sammaellAsked:
Who is Participating?
 
NightmanConnect With a Mentor CTOCommented:
Sorry, correction to that script:

SELECT t1.* FROM iv00112 t1 INNER JOIN iv00112 t2 ON  t1.LOCNCODE=t2.LOCNCODE AND t1.QTYTYPE =t2.QTYTYPE AND t1.ITEMNMBR=t2.ITEMNMBR AND t2.BIN= 'SERVICE'
WHERE t1.LOCNCODE= '106'  AND t1.BIN= 'recloser'
0
 
NightmanCTOCommented:
You already have a value in there - a primary key constraint means that you can only have one item with that unique value.

run SELECT * FROM iv00112 WHERE  locncode = '106'
and bin = 'SERVICE'

If this returns any data then you will not be able to update
0
 
sammaellAuthor Commented:
i have several rows where SERVICE is the bin value. im trying to make SERVICE the bin value for all rows that locncode = 106. i can go down item by item and change the bin value to be SERVICE, without error, its just not letting me do it all at once. i can definately have SERVICE as a value in the 'bin' field more than once, its not unique.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
NightmanCTOCommented:
What is the primary key of this table?
0
 
sammaellAuthor Commented:
column dex_row_id.
0
 
NightmanCTOCommented:
Can you script the table structure and PK and post it here? It doesn't seem as if this is actually the PK (unless you have triggers on the table that are doing something else)
0
 
sammaellAuthor Commented:
/****** Object:  Table [dbo].[IV00112]    Script Date: 11/13/2006 14:56:25 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[IV00112](
      [ITEMNMBR] [char](31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [LOCNCODE] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [BIN] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [QTYTYPE] [smallint] NOT NULL,
      [QUANTITY] [numeric](19, 5) NOT NULL,
      [ATYALLOC] [numeric](19, 5) NOT NULL,
      [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKIV00112] PRIMARY KEY NONCLUSTERED
(
      [ITEMNMBR] ASC,
      [LOCNCODE] ASC,
      [BIN] ASC,
      [QTYTYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
0
 
NightmanCTOCommented:
And if you run

SELECT ITEMNMBR,LOCNCODE,BIN,QTYTYPE FROM iv00112 WHERE LOCNCODE= '106'
and BIN= 'SERVICE'

What is returned?
0
 
sammaellAuthor Commented:
i get 57 rows returned.
0
 
rw3adminCommented:
run this
Select Count(Distinct Bin) from iv00112 where locncode = '106'
I bet you will return 2 or more

also you have a composite key on
(
     [ITEMNMBR] ASC,
     [LOCNCODE] ASC,
     [BIN] ASC,
     [QTYTYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

so its better if you are really explicit in your update

update iv00112
set bin = 'SERVICE'
where itemnmbr = '10003'  and
          locncode = '106' and
          bin ='recloser' and
          qtytype='Put the correct field here'
0
 
sammaellAuthor Commented:
i dont want to update the bin value one row at a time, there are over 2000 rows.
0
 
rw3adminCommented:
but then otherwise you are running into PK violation, and thats even worst.

I suggest write a cursor as
select ITEMNMBR,LOCNCODE,  BIN, QTYTYPE from iv00112

and step through each record and update data correctly, provided you wrote your cursor correctly this maybe the only fool proof way of updating any PK fields in iv00112
0
 
NightmanCTOCommented:
Basically, you will get a PK violation if you attempt to create the combination of ITEMNMBR, LOCNCODE, BIN, QTYTYPE more than once, as this has to be unique.

So while you may think that the update statement will work, there is at least one row that if updated will result in a duplicate. This will cause the entire batch to fail. So either write a cursor and update one by one (and ignore duplicates) or look at your data integrity.

SELECT t1.* FROM iv00112 t1 INNER JOIN iv00112 t2 ON  t1.LOCNCODE=t2.LOCNCODE AND t1.QTYTYPE =t2.QTYTYPE AND t1.ITEMNMBR=t2.ITEMNMBR
WHERE t1.LOCNCODE= '106'  AND t1.BIN= 'recloser'

This should show you the offending rows. Post the results here - if there are less than 20 ;)
0
 
rw3adminCommented:
try this, although I will not be doing this ... cursors are much safer

update       iv00112
set       bin = 'SERVICE'
From      iv00112 A
Left Join
      iv00112 B
On      A.ITEMNMBR=B.ITEMNMBR and
      A.LOCNCODE=B.LOCNCODE and
      A.QTYTYPE=B.QTYTYPE
where       A.locncode = '106' and
      A.bin = 'recloser' and
      B.BIN !='SERVICE'

or this


update       iv00112
set       bin = 'SERVICE'
From      iv00112
where       locncode = '106' and
      bin = 'recloser' and
      BIN !='SERVICE'
      
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.