Solved

T-SQL help

Posted on 2006-11-13
14
319 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

776 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