SQL Duplicate Error

Hi!

I am trying to do a simple udpate on a table and I am getting the
error:

"Cannot insert duplicate key row in object 'UserInfo' with unique
index 'UserInfo_Login'.The statement has been terminated."

Here is the command I am trying to run
---
USE Deve2_SITE

update UserInfo set tp_Login=N'DEV2\sam', tp_Email=N'test@test.com',
tp_Title=N'Samson, Sammy'
WHERE (tp_Login=N'DEV\sam')
--

Any ideas?

Re: SQL Duplicate Error

What it seems to be saying is that there is already a row in the table
with the value N'DEV2\sam', and the index UserInfo_Login has a unique
constraint that does not permit duplicates. However it is possible
the index has multiple columns. To know exactly you would need to
know the details of the definition of the index UserInfo_Login on the
table UserInfo. You could find that out by running:

EXEC sp_helpindex Deve2_SITE..UserInfo

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 09:51:15 -0700, SJ wrote:

>Hi!
>
>I am trying to do a simple udpate on a table and I am getting the
>error:
>
> "Cannot insert duplicate key row in object 'UserInfo' with unique
>index 'UserInfo_Login'.The statement has been terminated."
>
>Here is the command I am trying to run
>---
>USE Deve2_SITE
>
>update UserInfo set tp_Login=N'DEV2\sam', tp_Email=N'test@test.com',
>tp_Title=N'Samson, Sammy'
>WHERE (tp_Login=N'DEV\sam')

Re: SQL Duplicate Error

On Jun 20, 11:51 am, SJ wrote:
> Hi!
>
> I am trying to do a simple udpate on a table and I am getting the
> error:
>
> "Cannot insert duplicate key row in object 'UserInfo' with unique
> index 'UserInfo_Login'.The statement has been terminated."
>
> Here is the command I am trying to run
> ---
> USE Deve2_SITE
>
> update UserInfo set tp_Login=N'DEV2\sam', tp_Email=N't...@test.com',
> tp_Title=N'Samson, Sammy'
> WHERE (tp_Login=N'DEV\sam')
> --
>
> Any ideas?

In addition to Roy's advice, do you have triggers on your table?

http://sqlserver-tips.blogspot.com/

Re: SQL Duplicate Error

On Jun 20, 1:42 pm, Alex Kuznetsov wrote:
> On Jun 20, 11:51 am, SJ wrote:
>
>
>
>
>
> > Hi!
>
> > I am trying to do a simple udpate on a table and I am getting the
> > error:
>
> > "Cannot insert duplicate key row in object 'UserInfo' with unique
> > index 'UserInfo_Login'.The statement has been terminated."
>
> > Here is the command I am trying to run
> > ---
> > USE Deve2_SITE
>
> > update UserInfo set tp_Login=N'DEV2\sam', tp_Email=N't...@test.com',
> > tp_Title=N'Samson, Sammy'
> > WHERE (tp_Login=N'DEV\sam')
> > --
>
> > Any ideas?
>
> In addition to Roy's advice, do you have triggers on your table?
>
> http://sqlserver-tips.blogspot.com/- Hide quoted text -
>
> - Show quoted text -

Thanks all. I guess i dont know what an index is. And why an update
would matter. This is the result of the EXEC command:

UserInfo_FullText nonclustered, unique located on PRIMARY tp_GUID
UserInfo_Login nonclustered, unique located on PRIMARY tp_SiteID,
tp_Login, tp_Deleted
UserInfo_PK clustered, unique, primary key located on PRIMARY
tp_SiteID, tp_ID
UserInfo_SID nonclustered, unique located on PRIMARY tp_SiteID,
tp_SystemID

ANy ideas? Why is an update command doing an insert (that is probably
a dumb question)

Re: SQL Duplicate Error

>UserInfo_Login nonclustered, unique located on PRIMARY tp_SiteID,
>tp_Login, tp_Deleted

OK, the UNIQUE contraint is on three columns, the combination of
tp_SiteID, tp_Login and tp_Deleted.

The UPDATE command is not doing an INSERT, but it is changing the
value of the column so that the combination of those three columns
conflicts with data already in the table - it woud violate the UNIQUE
contraint.

If you run the query:

SELECT *
FROM Deve2_SITE..UserInfo
WHERE tp_Login IN ( N'DEV\sam', N'DEV2\sam')
ORDER BY tp_Login, tp_SiteID, tp_Deleted

That will show the rows that are in conflict. What you are looking
for is that there is already DEV2\sam for at least one matching pair
of tp_SiteID/tp_Deleted values.

Hope that helps.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 11:55:41 -0700, SJ wrote:

>On Jun 20, 1:42 pm, Alex Kuznetsov wrote:
>> On Jun 20, 11:51 am, SJ wrote:
>>
>> > Hi!
>>
>> > I am trying to do a simple udpate on a table and I am getting the
>> > error:
>>
>> > "Cannot insert duplicate key row in object 'UserInfo' with unique
>> > index 'UserInfo_Login'.The statement has been terminated."
>>
>> > Here is the command I am trying to run
>> > ---
>> > USE Deve2_SITE
>>
>> > update UserInfo set tp_Login=N'DEV2\sam', tp_Email=N't...@test.com',
>> > tp_Title=N'Samson, Sammy'
>> > WHERE (tp_Login=N'DEV\sam')
>> > --
>>
>> > Any ideas?
>>
>> In addition to Roy's advice, do you have triggers on your table?
>>
>> http://sqlserver-tips.blogspot.com/- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks all. I guess i dont know what an index is. And why an update
>would matter. This is the result of the EXEC command:
>
>UserInfo_FullText nonclustered, unique located on PRIMARY tp_GUID
>UserInfo_Login nonclustered, unique located on PRIMARY tp_SiteID,
>tp_Login, tp_Deleted
>UserInfo_PK clustered, unique, primary key located on PRIMARY
>tp_SiteID, tp_ID
>UserInfo_SID nonclustered, unique located on PRIMARY tp_SiteID,
>tp_SystemID
>
>ANy ideas? Why is an update command doing an insert (that is probably
>a dumb question)