Updating using the Set command 1

[Scooby62]

Hi there.

I'm having a problem updating a field for everyone in one of my tables. The query I have so far is:

UPDATE EmpBen LEFT JOIN Eml ON EmpBen.Emp= Eml.Emp
Set EmpCov=(Case when Eml.AnnlFrzSal>=50000 then (35000+(Eml.AnnlFrzSal-50000)*.50) Else Eml.AnnlFrzSal*.70 End)
Where EmpBen.Ben='BasicLTD 07' AND Eml.EmlEfdDt Is Null

The issue seems to be how to join a table in an Update Statement.

Any help would be much appreciated.

Thanks.

 

[mrdenny]

Something like this.

UPDATE a
 Set EmpCov=(Case when Eml.AnnlFrzSal>=50000 then (35000+(Eml.AnnlFrzSal-50000)*.50) Else Eml.AnnlFrzSal*.70 End)
from EmpBen a
LEFT JOIN Eml ON a.Emp= Eml.Emp
Where a.Ben='BasicLTD 07' AND Eml.EmlEfdDt Is Null

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Scooby62]

Wow.

Thank you so much.

Worked like a charm.

I really appreciate your help here.

Thanks again.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com