SQL Subselect in an Update

Question

Oh great Stackoverflow, I beseech thee... I need to do the following:

CREATE UNIQUE INDEX UserName ON Emp(UserName) -- Sheesh!

UPDATE Emp SET
UserName = Left(FirstName,1)+LastName

WHERE NOT exists an employee with that UserName already.

Answer 1

This will still allow duplicates... but if you want what you said, Try this (exactly what you said):

UPDATE Emp SET
   UserName = Left(FirstName,1) + LastName
Where Not Exists (Select * From Emp 
                  Where UserName = Left(FirstName,1) + LastName)

I'd guess, however, that you are trying to avoid duplicates, not just reset UserNames where no existing one is the same as the proposed new value. If so, you should add a unique index on the userName column, but first, run this:

UPDATE Emp SET
   UserName = Left(FirstName,1) + LastName
Where  Not Exists 
             (Select * From Emp 
              Where UserName = Left(FirstName,1) + LastName)
    And Not Exists 
             (Select * From Emp 
              Group By Left(FirstName,1) + LastName
              Having Count(*) > 1)

This will reset all the UserNames that CAN be reset without creating duplicates. (It will not eliminate existing Dupes). If there are no dupes after this, you can apply the index.

Answer 2

Yeah, incase you do not read further down username really should be Unique also i would make sure there is something else in the where clause because that is going to be an intense query.

You may consider writing a function to do this for you to add some other uniqueness or id to the end if it exists (Count(*) where userName like Username).

UPDATE Emp SET
UserName = Left(FirstName,1)+LastName
WHERE
UserName not in (Select UserName From Emp Where UserName = Left(FirstName,1) + LastName)

Answer 3

I would put an unique index on username and use:

UPDATE IGNORE Emp SET UserName = Left(FirstName,1)+LastName

If your database supports that ;)

Answer 4

In the first place, your username field should have a unique index. If you don;t then your data is at risk form someone updating without using your little update statement. Current code suggestions don't give you an example of how to handle the setting of the user name if the existing one is used, what do you want to do? This should be part of your process. User needs a username.

just to show here is one way to handle incrementing the username until you get one that doesn't exist. This example is an insert not an update, but I think you can use it as a starting place. BY using this method (and this is for one record at a time inserts), no one ever gets into the system with a duplicate username and errors are not thrown if an exact match exists, it justs moves on to find the next match.

CREATE PROC [dbo].[ins_MyUserTable]
(
@userID int,
@Login varchar(255)
)

As

DECLARE 

@ValidateUserName varchar(50),
@NewUserName varchar(50),
@CheckUserName varchar(50),
@Holding int

-- Get User Name (first letter of first name + last name)
SELECT @NewUserName = @Login
SET @Holding = 0

-- Examine User Name (must be unique)
SELECT @ValidateUserName = 1

WHILE @ValidateUserName = 1
  BEGIN
 -- Check for Login
 SELECT @CheckUserName = (SELECT TOP 1 login FROM MyUserTable WHERE login = @NewUserName)

 IF @CheckUserName = @NewUserName
   BEGIN
                 SELECT @Holding = @Holding + 1
  SELECT @NewUserName = @Login + Convert(varchar(2), @Holding)
   END
 ELSE
   BEGIN
  SELECT @ValidateUserName = 0
   END
  END
 -- Insert  into MyUserTable 
 INSERT INTO MyUserTable
   ([user_id], login)
 VALUES
   (@userID, @NewUserName)