Date: Wed, 8 Oct 2003 18:10:44 -0400 (EDT)
From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: UPDATE one table with values from another
Message-ID: <20031008180813.X53328@xeon.unixathome.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

I know there is a simple solution, but I can't remember what it is.  :(

I have two similar tables.  I want to update the fields from one table to
contain the values form the other.  The two tables are:

laptop.freshports.org=# \d commit_log_ports
   Table "public.commit_log_ports"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 commit_log_id | integer  | not null
 port_id       | integer  | not null
 needs_refresh | smallint | not null
 port_version  | text     |
 port_revision | text     |
Indexes: commit_log_ports_pkey primary key btree (commit_log_id, port_id),
         needs_refresh btree (needs_refresh)
Foreign Key constraints: $1 FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE,
                         $2 FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers: commit_log_ports_insert

laptop.freshports.org=# \d commit_log_ports_elements
Table "public.commit_log_ports_elements"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 commit_log_id | integer  | not null
 element_id    | integer  | not null
 needs_refresh | smallint | not null
 port_version  | text     |
 port_revision | text     |
Indexes: commit_log_ports_elements_pkey primary key btree (commit_log_id, element_id)
Foreign Key constraints: $1 FOREIGN KEY (commit_log_id) REFERENCES commit_log(id) ON UPDATE CASCADE ON DELETE CASCADE,
                         $2 FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE CASCADE ON DELETE CASCADE

laptop.freshports.org=#

I can obtain the values I want with this query:

SELECT CLP.*
  FROM commit_log_ports CLP, ports P, commit_log_ports_elements X
 WHERE CLP.port_id       = P.id
   AND CLP.commit_log_id = X.commit_log_id
   AND X.element_id      = P.element_id;


I started writing the UPDATE and got as far as this before brain fatigue set in:

UPDATE commit_log_ports_elements X
   SET X.needs_refresh = CLP.needs_refresh,
       X.port_version  = CLP.port_version,
       X.port_revision = CLP.port_revision
WHERE X.commit_log_id = commit_log_ports CLP
  AND X.

A clue please?  Thank you.