Solved

T-SQL help

Posted on 2006-11-13
14
317 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:sammaell
  • 6
  • 5
  • 3
14 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17933215
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
 

Author Comment

by:sammaell
ID: 17933262
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17933273
What is the primary key of this table?
0
 

Author Comment

by:sammaell
ID: 17933363
column dex_row_id.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17933403
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
 

Author Comment

by:sammaell
ID: 17933441
/****** 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
 
LVL 29

Expert Comment

by:Nightman
ID: 17933476
And if you run

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

What is returned?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:sammaell
ID: 17933631
i get 57 rows returned.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17933636
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
 

Author Comment

by:sammaell
ID: 17933946
i dont want to update the bin value one row at a time, there are over 2000 rows.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17934011
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17934093
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
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17934104
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17934167
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now