UPDATE one table with values from another

From: Dan Langille <dan(at)langille(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: UPDATE one table with values from another
Date: 2003-10-08 22:10:44
Message-ID: 20031008180813.X53328@xeon.unixathome.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2003-10-08 23:37:57 Re: UPDATE one table with values from another
Previous Message Thomas Wegner 2003-10-08 19:38:00 Re: Oracle 'connect by prior' now eaiser in 7.3?