Using a CTE for an update - Mailing list pgsql-general

I would think this would be possible.  I'm on 9.0.8

I have a reference between two tables, and want to populate a field in one table
with a value that's in the referenced table ( based on the FK reference of course ).

with row as ( select my.atmos_site_id, my.stationid from my_stations my, atmos_sites asites where my.atmos_site_id =
asites.id) 
update atmos_sites set stationid = row.stationid where id = row.atmos_site_id;

I get a syntax error at "update".  Seems updates don't act quite like selects.

Do I need to write a function that iterates to do an update like this?

thanks in advance,

-ds