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.
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? |