Update, Set AND Join Query

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

 

[PHV]

In JetSQL you have to use the IIf function instead of the standard CASE ... WHEN ...:
UPDATE EmpBen INNER JOIN Eml ON EmpBen.Emp = Eml.Emp
SET EmpCov = IIf(Eml.AnnlFrzSal>=50000,35000+(Eml.AnnlFrzSal-50000)*.50,Eml.AnnlFrzSal*.70)
WHERE EmpBen.Ben='BasicLTD 07' AND Eml.EmlEfdDt Is Null

As you use Eml.AnnlFrzSal in the SET clause I think an inner join is more appropriate.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Scooby62]

Hey PHV.

I guess I should have told you I'm working on an SQL server in SQL and not JetSQL.

Still having probs.

Thanks for the quick response though.

 

[PHV]

I'm afraid you've posted in a wrong forum.
Perhaps here ?
forum183

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Scooby62]

Correct.

Thanks for your help and direction.