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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
