Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1E04F9F9F91 for ; Thu, 27 Sep 2007 05:29:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 54242-06 for ; Thu, 27 Sep 2007 05:29:28 -0300 (ADT) X-Greylist: delayed 00:06:39.656845 by SQLgrey-1.7.5 Received: from mail.prosyst.com (ns.prosyst.com [195.14.248.50]) by postgresql.org (Postfix) with ESMTP id 79A989F9BD9 for ; Thu, 27 Sep 2007 05:29:32 -0300 (ADT) Received: (qmail 13503 invoked from network); 27 Sep 2007 08:15:05 -0000 Received: from wizard.prosyst.com (HELO ?192.168.1.239?) (195.14.248.49) by mail.prosyst.com with (DHE-RSA-AES256-SHA encrypted) SMTP; 27 Sep 2007 08:15:05 -0000 Message-ID: <46FB6822.9060507@prosyst.com> Date: Thu, 27 Sep 2007 10:21:54 +0200 From: Venelin Arnaudov User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Problems with bulk update Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200709/154 X-Sequence-Number: 29367 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