Avoid duplicate update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Avoid duplicate update

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2010-05-04 : 10:31:28

I need to create this query:

I have an application where the user can select a team and each email that is belongs to the team will be displayed. Best to give an example to show you:

I have an EmailAddresses table with the following fields

EmailAddressID (Identity, auto incremented when add a new one)
TeamID (foreign key identifying the team)
EmailAddress

Lets say that "A Team" has two emails

joe@email.com
pat@email.com

and if the user updates the second email so now there is the following emails:

joe@email.com
joe@email.com

This is what I want to avoid, having the same email twice for a certain team.

I think that I Need an if statement or case statement to prevent this something like this (PSEUDO_Code)


IF (SELECT COUNT(*) FROM EmailAddresses WHERE TeamID=@currentTeamID and EmailAddress=@editedEmailAddress) < 1 Then
     UPDATE EmailAddresses SET EmailAddress = @editedEmailAddress
     WHERE EmailAddressID=@editedEmailAddressID
End IF

Has anyone done something like this before? thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-04 : 10:41:35

You should create unique index on columns TeamID and EmailAddress like this:


CREATE UNIQUE NONCLUSTERED INDEX EmailAddresses_IX_TeamID_EmailAddress
   ON EmailAddresses(TeamID, EmailAddress);

MyronCope
Starting Member

46 Posts

Posted - 2010-05-04 : 11:11:03

thanks for the advice. what will be returned when I try to insert a duplicate with the unique index setup?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-04 : 11:16:05

Depends what application language you are using.

You'll get some sort of exception that you can handle in your calling application if you try inserting / updating into a unique constraint.

Or you can use a TRY CATCH block in your accessor stored proc(s) if you have implemented them

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources