How can I correct the following so that I don't receive a syntax error in Microsoft SQL Server 2005?
UPDATE Emp E
SET UserName = Left(FirstName,1)+LastName
WHERE EmpID=1
AND NOT EXISTS(
SELECT * FROM Emp
WHERE UserName=Left(E.FirstName,1)+E.LastName
)
To alias the name you must use FROM:
UPDATE Emp
SET UserName = Left(FirstName,1)+LastName
FROM Emp E
WHERE NOT EXISTS(
SELECT * FROM Emp
WHERE UserName=Left(E.FirstName,1)+E.LastName)
Or alias the sub-query:
UPDATE Emp
SET UserName = Left(FirstName,1)+LastName
WHERE NOT EXISTS(
SELECT * FROM Emp E
WHERE E.UserName=Left(Emp.FirstName,1)+Emp.LastName)
Untested...
UPDATE E
SET UserName = Left(FirstName,1)+LastName
FROM Emp E
WHERE NOT EXISTS(
SELECT * FROM Emp
WHERE UserName=Left(E.FirstName,1)+E.LastName
)
there are 2 syntaxes here. To use an alias as the target of the update you do the following:
UPDATE e
SET UserName = Left(FirstName,1)+LastName
FROM Emp e
WHERE NOT EXISTS(
SELECT * FROM Emp
WHERE UserName=Left(E.FirstName,1)+E.LastName
)
AND EmpID=1
If I'm understanding correctly, this is what you're trying to do. Though, I'm not sure the first part of the WHERE clause is really necessary unless there's a ton of rows...
UPDATE Emp
SET UserName = Left(FirstName,1)+LastName
WHERE UserName<>Left(FirstName,1)+LastName
AND EmpID=1
It's been a while since I've tried this syntax... but in SQL Server you can specify a from on an update.
UPDATE Emp SET
UserName = Left(FirstName,1)+LastName
FROM Emp e1
WHERE NOT EXISTS (
SELECT *
FROM Emp e2
WHERE e2.UserName=Left(e1.FirstName,1)+e1.LastName
)
EDIT: My syntax certainly runs but I'm not certain that it's correct. Regardless of whether or not it's right, I would suggest using the alias in the update statement just to ensure that others can better understand what you are doing.
UPDATE e1 SET
...
FROM Emp e1
...
