[SQL] Problems with bulk update

[Venelin Arnaudov]

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 table1
 set table1.field2_new=table2.new_id
 from table2
 where 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'
  LANGUAGE SQL;

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster