From: Venelin Arnaudov <v.arnaudov@prosyst.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with bulk update
Date: Thu, 27 Sep 2007 12:38:03 +0200
Message-ID: <46FB880B.70003@prosyst.com> (raw)
In-Reply-To: <46FB776D.5080406@archonet.com>
References: <46FB6822.9060507@prosyst.com>
<46FB776D.5080406@archonet.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: v.arnaudov@prosyst.com, dev@archonet.com
Subject: Re: Problems with bulk update
In-Reply-To: <46FB880B.70003@prosyst.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox