Primary key violation on update

I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-JohnPlease post your DDL + INSERT statements of sample data that are causing the
problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:t7gqs15s3a72ddh2ipem4a80bq8osrng9r@.
4ax.com...
I am getting a primary key violation with an update.
My update statement is like
update e
set e.EID = neid.New_EID
from EmpTable e
inner join NewEID neid on neid.Tech_SSN = e.SSN
and neid.New_EID not in (Select EID from EmpTable)
The problem is that the final (Select EID from EmpTable) is not
refreshed after each update so I end up trying to set the EID to an
existing EID (sometimes there are more than one EID with the same
SSN).
Ideas? Thanks.
-John|||When you use JOIN with non-unique columns in a t-SQL UPDATE statement, you
could get that error. Please post your table structures & sample data along
with expected results for others to better understand and repro your
problem. For details, refer to: www.aspfaq.com/5006
Here is an untested attempt, based on the assumption that you have duplicate
EIDs for each SSN which in turn are duplicates as well:
UPDATE EmpTable
SET EID = ( SELECT MAX( neid.New_EID )
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN
AND NOT EXISTS ( SELECT *
FROM EmpTable emp
WHERE emp.EID = neid.New_EID ) )
WHERE EXISTS( SELECT *
FROM NewEID neid
WHERE neid.Tech_SSN = EmpTable.SSN ) ;
Do you have keys in all your tables? Keys are mandatory in all tables.
Without them, you are mostly left with unmanageable mess.
Anith|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:

>Please post your DDL + INSERT statements of sample data that are causing th
e
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||What are the desired results of the UPDATE, i.e. what should the rows in
EmpTable look like after the update?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Baima" <john@.nospam.com> wrote in message
news:9qhqs19sf1e2qi3nqc29mq0ud9p8terd1a@.
4ax.com...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote:

>Please post your DDL + INSERT statements of sample data that are causing
>the
>problem.
CREATE TABLE [EmpTable] (
[EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ssn] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EmpTable_1] PRIMARY KEY CLUSTERED
(
[EID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [NewEID] (
[Tech_SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Tech_EID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
Insert ('12345', '111111111) into EmpTable
Insert ('12346', '111111111) into EmpTable
Insert ('111111111', '123456') into NewEID
The first record in EmpTable can up updated, but when it hits the
second, the update fails.
-John|||"Anith Sen" <anith@.bizdatasolutions.com> wrote:

>Do you have keys in all your tables? Keys are mandatory in all tables.
>Without them, you are mostly left with unmanageable mess.
I posted the DDL just after this post. Keys are not mandatory but what
I inherited is certainly a mess.
-John|||>>Do you have keys in all your tables? Keys are mandatory in all tables.
> I posted the DDL just after this post. Keys are not mandatory
I think he meant theoretically, not technically. What is a table without a
key? In most cases, a mess, by definition.|||John Baima <john@.nospam.com> wrote:

>I am getting a primary key violation with an update.
As I've looked at these tables for awhile, I think that I can solve
the problem by filtering out duplicates. Is there a general way of
finding duplicate records and then deleting just one? In this case, I
really don't care which record is deleted.
Thanks!
-John|||>> Is there a general way of finding duplicate records and then deleting
KBA ( support.microsoft.com ): 139444
Anith|||On Tue, 17 Jan 2006 19:15:06 GMT, John Baima wrote:

>I am getting a primary key violation with an update.
>My update statement is like
>update e
> set e.EID = neid.New_EID
>from EmpTable e
> inner join NewEID neid on neid.Tech_SSN = e.SSN
>and neid.New_EID not in (Select EID from EmpTable)
>The problem is that the final (Select EID from EmpTable) is not
>refreshed after each update
(snip)
Hi John,
"Each update"? There's only one update in this code!
Remember that SQL is a set-based language. That extends to the
interpretation of data modification statements as well. Rows are not
updated one by one, as you seem to think. The entire results of the
UPDATE statement are first built in a temp holding place; after that,
all affected rows are changed, all at the same time.
The actual implementation doesn't have to follow this to the letter, but
the results should be the same as if it does.
Hugo Kornelis, SQL Server MVP