From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: UPDATE one table with values from another
Date: Wed, 8 Oct 2003 18:10:44 -0400 (EDT)
Message-ID: <20031008180813.X53328@xeon.unixathome.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Subject: Re: UPDATE one table with values from another
In-Reply-To: <20031008180813.X53328@xeon.unixathome.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox