Re: Synchronizing a table that is in two different databases : Need to dump a table as inserts from db1 and change the insert statements into UPDATE statements

I think the convention on this list is to bottom-post.

On Fri, Mar 28, 2014 at 7:59 AM, Khangelani Gama <kgama(at)argility(dot)com> wrote:

> Hi Chris
>
>
>
> I did replace foo and bar tables with my tables, in db2 I have table
> foo(the one to be updated) and the table bar(the one which has data I got
> from db1)
>
>
>
> *From:* ccurvey(at)gmail(dot)com [mailto:ccurvey(at)gmail(dot)com] *On Behalf Of *Chris
> Curvey
> *Sent:* Friday, March 28, 2014 1:50 PM
> *To:* Khangelani Gama
> *Cc:* pgsql
> *Subject:* Re: [GENERAL] Synchronizing a table that is in two different
> databases : Need to dump a table as inserts from db1 and change the insert
> statements into UPDATE statements
>
>
>
>
>
>
>
> On Fri, Mar 28, 2014 at 5:56 AM, Khangelani Gama <kgama(at)argility(dot)com>
> wrote:
>
> Hi Chris or anyone who can help
>
>
>
> When I try this just below, it complains about foo , saying "ERROR:
> schema "foo" does not exist." I got stuck on this error for a while now
> but still trying to see why but still no luck so far. If you have something
> please help
>
>
>
>
>
> begin;
>
>
>
> update foo
>
> set br_desc = bar.br_desc
>
> , br_active = bar.br_active
>
> (rest of columns)
>
> where foo.br_cde = bar.br_cde;
>
>
>
>
>
> you need to replace "foo" and "bar" with the names of your tables.
>

I'm stumped. Here's the test that I just ran on both 9.2 and 8.4, and I'm
not getting an error:

drop table if exists foo;
drop table if exists bar;

create table foo (a int, b int);
create table bar (a int, b int);

insert into foo values (1, 1);
insert into foo values (2, 2);

insert into bar values (1, 3);
insert into bar values (4, 4);

update foo
set b = bar.b
from bar
where foo.a = bar.a;

select * from bar;

Perhaps if you want to privately send me the exact statement you are using,
I can take quick look and see if there's something obvious.