Solved

UPDATE FROM TABLE gets 'Violation of UNIQUE KEY constraint'

Posted on 2006-11-13
12
352 Views
Last Modified: 2012-05-05
Here is my table structure...
TABLE TOC
TocId = int (PK)
ParentID = int
Name = nvarchar(255) (No Nulls)
LastModified = datetime (No Nulls)
CreateDate = datetime (No Nulls)
InheritAcl = bit (No Nulls)
IsIndexed = bit (No Nulls)
VolumeID = smallint
TemplateID = smallint
MinLevel = smallint
MaxLevel = smallint
LinkTo = int
LocationID = int
Creator = nvarchar(128)

Table TD4
TocID = int (No Nulls)
[Last Name] = nvarchar(20)
[First Name] = nvarchar(15)
[Date Of Birth] = datetime
[School Name] = nvarchar(25)
Status = nvarchar(10)
[Student Number] = nvarchar(8)

Before anyone starts yelling at me about the table design... I'm replacing the last DBA who obviously didn't know what he was doing... Also this is a 3rd party database.
What I need to do is put a standard name inside the TOC.Name.  The previous standard was "Last_Name, First_Name"  which the department wants to stick with.
I have to replace any of the name fields which start with '00' which are currently misnamed.
This is the query I came up with to do this...

UPDATE TOC
SET TOC.Name = (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
INNER JOIN TD4
ON TOC.TOCID = TD4.TOCID
WHERE TD4.[Last Name] IS NOT NULL AND TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%'

When I run this i get the following message...

Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_Toc'. Cannot insert duplicate key in object 'Toc'.
The statement has been terminated.

Please keep in mind that this database links to a piece of 3rd party software.  This means that I cannot create a view, nor can I create a qurey from within the program.  I do have another way to do this but the 3rd party software will take at least 72 hours because it goes through each record and identifies a specific file, then moves the file then updates the database.

Please help, as this is urgent and needs to be completed sometime today if not first thing tomorrow morning.
0
Comment
Question by:bgernertt
12 Comments
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 100 total points
ID: 17934306
Quick and ugly (exludes those that may already exist):

UPDATE TOC
SET TOC.Name = (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
INNER JOIN TD4
ON TOC.TOCID = TD4.TOCID
WHERE TD4.[Last Name] IS NOT NULL AND TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%'
AND TOC.Name NOT IN (SELECT [Last Name] + ', ' + TD4.[First Name] FROM TOCID WHERE [Last Name] IS NOT NULL AND [First Name] IS NOT NULL AND TOC.NAME LIKE '00%')

Bearing in mind that if Name is a unique constraint and you have two 'Smith, John' in the database this will fail with the same error
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 400 total points
ID: 17934344
its obvious that TOC.Name field has a unique constraint on it

0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17934414
Nightman, didnt you mean something like this

UPDATE       TOC
SET       TOC.Name = (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
INNER JOIN
      TD4
ON       TOC.TOCID = TD4.TOCID
WHERE       TD4.[Last Name] IS NOT NULL AND
      TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%' and
      TOC.NAME Not in (      
      Select      [Last Name] + ', ' + [First Name]
      FROM       TD4
      WHERE       [Last Name] IS NOT NULL AND
            [First Name] IS NOT NULL )
0
 
LVL 2

Author Comment

by:bgernertt
ID: 17934563
TOC.Name does not have a constraint on it.  I have double checked that one.  The only constraint is on TOC.TOCID
RW3.  With your code it still throws the error,
Nightman, after fixing some of the code you posted, it still throws the error.
btw.. I am a sa in the current DB, if that comes up any time soon.
0
 
LVL 2

Author Comment

by:bgernertt
ID: 17934614
Just so you guys know for a fact that TOC.Name does not have a constraint on it... here is the results of the following query, which i know produces duplicates.

SELECT NAME
FROM TOC
inner join td4
on toc.tocid = td4.tocid
WHERE toc.NAME LIKE '00%'
order by toc.name

RESULTS:
00000001
00000001
00000002
00000002
00000003
00000003
00000004
00000004
00000005
00000005
00000006
00000006

0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17934774
you said it throws error...whats the error?

UPDATE      TOC
SET      TOC.Name = (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
INNER JOIN
          TD4
ON      TOC.TOCID = TD4.TOCID
WHERE      TD4.[Last Name] IS NOT NULL AND
                 TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%' and
                 TOC.NAME Not in (    
     Select     [Last Name] + ', ' + [First Name]
     FROM      TD4
     WHERE      [Last Name] IS NOT NULL AND
          [First Name] IS NOT NULL )
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Expert Comment

by:rw3admin
ID: 17934792
also based on your query result where you are getting dups, all I can say are there any triggers on TOC table?,
maybe some trigger is throwing this error.
0
 
LVL 2

Author Comment

by:bgernertt
ID: 17934927
Error thrown is original error.
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_Toc'. Cannot insert duplicate key in object 'Toc'.
The statement has been terminated.

second
The dups are not actual dups... they have the same name, but different data.

proof query

SELECT NAME, TOC.tocid
FROM TOC
inner join td4
on toc.tocid = td4.tocid
WHERE toc.NAME LIKE '00%'
order by toc.name

RESULTS
00000001    58300
00000001    85014      
00000002    85015      
00000002    58301      
00000003    58302      
00000003    85016      
00000004    85017      
00000004    58303      
00000005    58304      
00000005    85018      
00000006    85019      
00000006    58305      
00000007    85020      
00000008    85021

Lastly...  I talked to the boss, and he said to do it the safe way through the 3rd party software, even if it takes 72 processing hours... ARRRRGGGHHH!
So i'll distribute the points for effort if the query problem isn't solved tomorrow morning.  I'll check the status of this tomorrow at 7:30a my time.
0
 
LVL 12

Expert Comment

by:jahboite
ID: 17935071
could it be empty strings in TD4.[Last Name] or TD4.[First Name]

could you try this to check your update query:

select (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
INNER JOIN TD4
ON TOC.TOCID = TD4.TOCID
WHERE TD4.[Last Name] IS NOT NULL AND TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%'

would this work:

select (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
where (select TD4.TOCID from TD4 where TD4.TOCID = TOC.TOCID AND TD4.[Last Name] IS NOT NULL AND TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%')

and then do

UPDATE TOC
SET TOC.Name = (TD4.[Last Name] + ', ' + TD4.[First Name])
FROM TOC
where (select TD4.TOCID from TD4 where TD4.TOCID = TOC.TOCID AND TD4.[Last Name] IS NOT NULL AND TD4.[First Name] IS NOT NULL AND TOC.NAME LIKE '00%')
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17935170
I wonder if TOC is a table or a view, since third party software is involved I bet they have added some quirkiness to the design so you are forced to use them for all solution, also now I am also leaning towards if its a table and its got some sort of after update or for update tirgger thats throwing that error.

lets do this .... I know you wont but what the heck

UPDATE    TOC
SET          TOC.Name = 'CHANGEME'
WHERE      TOC.NAME LIKE '00%'

this way we have eliminated the TD4 table and some possible quirkiness, if this works then we can come back and update TOC.NAME with a cursor or a loop... doing one record at a time.

0
 
LVL 2

Author Comment

by:bgernertt
ID: 17938859
Sorry guys, nothing worked.
And in response to the last 2 posts.
Jahboite:
Select queries may work without the Inner Join, but Updates definitely will not work, at least not in SQL 2k.  It gives me a syntax error on () around TD4.[First Name] & TD4.[Last Name] because it thinks that TD4 is an unrecognized expression.  As for the other Select queries, I tried these before I even did the update.  They all worked.

Rw3:
I am 100% sure that TOC is a table as it is listed inside 'Tables' inside Enterprise Mgr.  Also there is a Primary Key on the TOCID inside the table TOC (mind you not a secondary constraint).  Thus TOC must be a table.

I'll divy up the points in a minute.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17939207
What about any triggers on this table?, can you right click on this table name in EM then click on 'All Tasks'>>'Manage Triggers'  and check the Name dropdown list for any triggers?

Thanks for the points, but I would be more happy if I was able to help you in anyway, getting points while issue is not resolved doesnt really make me happy.

rw3admin
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

707 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

19 Experts available now in Live!

Get 1:1 Help Now