Problems with bulk update - Mailing list pgsql-sql

Hi

I want to update the values of one column of a table based on the
matches in a second table

data_table: field1, field2_new, ... field2_old

mapping_table: new_id, old_id

something like
update table1set table1.field2_new=table2.new_idfrom table2where table1.field2_old=table2.old_id;

Is this possible with postgre without writing php script that cycles the
data_table?

I have even created a function
CREATE FUNCTION get_new_field2(integer) RETURNS integer AS 'SELECT new_id FROM mapping WHERE old_id= $1 limit 1'
LANGUAGESQL;
 

and tried
UPDATE table1 set field2_new=get_new_field2(field2_old);
but it did not work :( It seems that the function is not executed for
each record of the data_table.


Kindest regards,
Venelin Arnaudov