Here is my table structure...
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)
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...
SET TOC.Name = (TD4.[Last Name] + ', ' + TD4.[First Name])
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.