Thread: Update table with data from another table

Hi,

I was hoping to update the results of one table with data from another table.  I have done this many times before using
UPDATEX SET Z FROM Y. 

The catch this time is the tables involved both have the same column names, same table names but reside in different
schemas.

UPDATE schema1.A
SET col = z.col
FROM schema2.A z
WHERE z.match = match;

I can't get postgres to accept the above or any variation such as adding the full schema and table name to each column.
I also can't seem to declare an alias for the destination table (UPDATE schema1.A w SET w.col = z.col). 

As a workaround I will rename one of the tables temporarily.  If someone has been able to get this to work I would
appreciateany tips to reference for next time. 

Mike

UPDATE schema1.A
SET col = z.col
FROM schema2.A z
WHERE z.match = schema1.A.match

This should always work. Observe schema1.A.match in the last line of the
query. If this does not work please post the error message you are
getting when you tried this.

-Chandra Sekhar Surapaneni

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike G.
Sent: Thursday, February 09, 2006 2:21 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Update table with data from another table

Hi,

I was hoping to update the results of one table with data from another
table.  I have done this many times before using UPDATE X SET Z FROM Y.

The catch this time is the tables involved both have the same column
names, same table names but reside in different schemas.

UPDATE schema1.A
SET col = z.col
FROM schema2.A z
WHERE z.match = match;

I can't get postgres to accept the above or any variation such as adding
the full schema and table name to each column.  I also can't seem to
declare an alias for the destination table (UPDATE schema1.A w SET w.col
= z.col).

As a workaround I will rename one of the tables temporarily.  If someone
has been able to get this to work I would appreciate any tips to
reference for next time.

Mike

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