[SQL] Problems with bulk update

2007-09-27 Thread 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

Re: [SQL] Problems with bulk update

2007-09-27 Thread Richard Huxton 

Venelin Arnaudov wrote:

Hi

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



something like
update table1
 set table1.field2_new=table2.new_id
 from table2
 where table1.field2_old=table2.old_id;


Did you try it?

http://www.postgresql.org/docs/8.2/static/sql-update.html

PG has a non-standard "FROM" extension for just this purpose.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Problems with bulk update

2007-09-27 Thread Venelin Arnaudov 
I have tried this
gforge=> UPDATE data_table
gforge->SET field2_new=(SELECT new_id FROM mapping_table WHERE
old_id= data_table.field2_old);

and

gforge-> update data_table set field2_new_=mt.new_id FROM data_table dt,
mapping_table mt WHERE dt.field2_old=mt.old_id;

but initially I got error. "ERROR:  column data_table.old_brand_id does
not exist"


It took me an eternity to realize that it is because of a missing entry
in the mapping table. I have added the record and the update run correctly.

Thank you very much

Kindest regards,
Venelin Arnaudov




Richard Huxton wrote:
> Venelin Arnaudov wrote:
>> Hi
>>
>> I want to update the values of one column of a table based on the
>> matches in a second table
>
>> something like
>> update table1
>>  set table1.field2_new=table2.new_id
>>  from table2
>>  where table1.field2_old=table2.old_id;
>
> Did you try it?
>
> http://www.postgresql.org/docs/8.2/static/sql-update.html
>
> PG has a non-standard "FROM" extension for just this purpose.
>

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org