pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE one table with values from another
4+ messages / 3 participants
[nested] [flat]
* UPDATE one table with values from another
@ 2003-10-08 22:10 Dan Langille <dan@langille.org>
0 siblings, 1 reply; 4+ messages in thread
From: Dan Langille @ 2003-10-08 22:10 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE one table with values from another
@ 2003-10-08 23:37 Josh Berkus <josh@agliodbs.com>
parent: Dan Langille <dan@langille.org>
0 siblings, 2 replies; 4+ messages in thread
From: Josh Berkus @ 2003-10-08 23:37 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; pgsql-sql
Dan,
> 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
FROM commit_log_ports CLP
WHERE X.commit_log_id = CLP.commit_log_id
You can always ask this kind of thing on IRC .....
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE one table with values from another
@ 2003-10-09 00:12 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-10-09 00:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Dan Langille <dan@langille.org>; pgsql-sql
On Wed, 8 Oct 2003, Josh Berkus wrote:
> > UPDATE commit_log_ports_elements X
IIRC, PostgreSQL doesn't like aliases of the
update table, so I think you'll need to spell it
out in the WHERE.
> > SET X.needs_refresh = CLP.needs_refresh,
> > X.port_version = CLP.port_version,
> > X.port_revision = CLP.port_revision
I don't think you need the X.'s here anyway, there's
only one update table.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE one table with values from another
@ 2003-10-09 03:41 Dan Langille <dan@langille.org>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dan Langille @ 2003-10-09 03:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Wed, 8 Oct 2003, Josh Berkus wrote:
> Dan,
>
> > 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
>
> FROM commit_log_ports CLP
> WHERE X.commit_log_id = CLP.commit_log_id
Thanks Josh. After a 5 hour drive to Hamilton, my brain was only capable
of doing the email.
> You can always ask this kind of thing on IRC .....
If I'd been at home, I would have. This laptop of mine is getting pretty
old. It took pretty close to 15 minutes for it to set a field to zero in
91,295 rows.... I need more ram and a faster laptop!
cheers
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-10-09 03:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-08 22:10 UPDATE one table with values from another Dan Langille <dan@langille.org>
2003-10-08 23:37 ` Josh Berkus <josh@agliodbs.com>
2003-10-09 00:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-09 03:41 ` Dan Langille <dan@langille.org>
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