Update using an Innerjoin 1

[szaunbr]

Hello. I am working with an informix database and need to update using an innerjoin.

I have one table: deduction that I need to update one field, the active field. I need to update every record for one group of employees only, but the group code is in another table, the employeemaster table.

I have tried this:

update deduction
inner join employeemaster on
deduction.emp = employeemaster.emp
set deduction.active = 'N'
where employeemaster.group = 'B';

but that gives me a syntax error

I have also tried:

update deduction
set deduction.active='N'
from employeemaster
where deduction.emp = employeemaster.emp
and employeemaster.group = 'B'

but that gives me a syntax error at "from"
If I eliminate the from line,
it give me an error that column employeemaster is not found in the query.

Please help.

thanks in advance.

 

[PHV]

UPDATE deduction
SET active = 'N'
WHERE emp IN (SELECT emp FROM employeemaster M WHERE M.group = 'B')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[szaunbr]

Thank you so much. That worked like a charm.