CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005sql-update
Phillip Senn
Phillip Senn

Reputation: 47663

Syntax error on SQL update statement

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
)

Upvotes: 0

Views: 321

Answers (6)

Mayo
Mayo

Reputation: 10812

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
...

Upvotes: 0

Hellena Handcart
Hellena Handcart

Reputation:

UPDATE Emp SET UserName = Left(FirstName,1)+LastName WHERE NOT EXISTS ( SELECT * FROM Emp e WHERE e.UserName=Left(emp.FirstName,1)+emp.LastName )

Upvotes: 0

atfergs
atfergs

Reputation: 1684

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

Upvotes: 0

Peter Oehlert
Peter Oehlert

Reputation: 16968

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

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294467

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)

Upvotes: 1

TGnat
TGnat

Reputation: 4011

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
                )

Upvotes: 1

Related Questions