Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

T-SQL help

Posted on 2006-11-13
14
Medium Priority
?
328 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

579 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