If primary Key is the same, then change it..

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

General SQL Server Forums

New to SQL Server Programming

If primary Key is the same, then change it..

Author

Topic

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-16 : 08:47:38

Ok so let you ask you guys this, what about a situation where changefrom table has a primary key .UserName that is already in changeto table's .UserName. I that case i would want to change the changeto's (destination table) .UserName to the vaulue plus and extra character like an "X" to allow the import of the new user with the re-issued UserName field. Do you all think that is possible?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 08:58:01

You seriously mean that a UserName is the primary key?
You seriously mean you can avoid duplicates this way?
I would fear that.

No, you're never too old to Yak'n'Roll if you're too young to die.

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-16 : 09:04:45

Well our computing services for whatever reason decided to re-use outlook names which is our primary key (at least i made sense before since they should never be duplicated). I was thinking that the SSN field should be the new primary key, but our system uses LDAP to verify the username so having 2 usernames the same won't work. I thought of an alternative to have a "pre-report" that says something like if username is in changefrom AND in changeto with a different SSN, then list here and then just update the contact information in changeto, so that my staff would be alterted that a username has been reused so they can manually merge the book requests to a "username_x" account to save the history of the old account and so that the new person would not see the old person's requests.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 09:24:22

Test this please:

update ct
set UserName=UseName+'X'
from changeto ct
join changefrom cf
on cf.UserName = ct.Username and cf.SSN <> ct.SSN

No, you're never too old to Yak'n'Roll if you're too young to die.

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-16 : 09:44:15

update ct
set UserName=UserName+'X'
from ULchangeto ct
join ULchangefrom cf
on cf.UserName = ct.UserName and cf.SSN <> ct.SSN

gets me this...

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'UserName'.

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-16 : 09:49:19

but this works

update ULchangeto
set UserName='UserNameX'
from ULchangeto ct
join ULchangefrom cf
on cf.UserName = ct.UserName and cf.SSN <> ct.SSN

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-16 : 09:51:41

but it will actually say "usernamex" so i'll need to find a way to properly syntax the UserName + 'X'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-16 : 10:05:36

update ct
set UserName=ct.UserName+'X'
from changeto ct
join changefrom cf
on cf.UserName = ct.Username and cf.SSN <> ct.SSN