Update based on criteria in different tables

[Coxylaad]

I want to update all the records in a table that satisfy a criteria on a field in a different related table.

This is probablies a bit of a newbie question. I have tried this:

update contract
set contract.contractceiling = contractceiling * 1.5
where contract.organisationcode = organisation.organisationcode
and organisation.addresscode = address.addresscode
and address.postcodefirstpart = 'NE31'

but it doesnt like having more than one table in the initial statement.
Removing the linking tables just causes an error further down the query (unrecognised tables)

any one help?

 

[swampBoogie]

update contract
set contractceiling = contractceiling * 1.5
where organisationcode in (select 
organisationcode from organisation join address
on organisation.addresscode = address.addresscode
where address.postcodefirstpart = 'NE31')

 

[Coxylaad]

thanks mate - you are the king!

 

[bperry]

I think this also might work (untested):

update contract
set contract.contractceiling
= contractceiling * 1.5
from Contract c
inner join Organisation o
on c.organisationcode = o.organisationcode
inner join Address a
on o.AddressCode = a.AddressCode
Where a.postcodefirstpart = 'NE31'