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 nullport_id | integer | not
nullneeds_refresh| smallint | not nullport_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 nullelement_id | integer | not
nullneeds_refresh| smallint | not nullport_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 XWHERE 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.
