pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to copy data between joined columns?
5+ messages / 3 participants
[nested] [flat]
* How to copy data between joined columns?
@ 2006-04-07 20:32 Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 20:34 ` Re: How to copy data between joined columns? Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bryce Nesbitt @ 2006-04-07 20:32 UTC (permalink / raw)
To: pgsql-sql
I have a need to copy/update data from one column to another, based on a
join condition. Is this easy to do in pure SQL? I have google'ed
without luck for a easy solution (that's not Microsoft specific, that
is). For example:
postgres=#
select xx_thing.date_start,xx_note.created_date
from xx_thing
join xx_issue using (thing_id) join xx_note using (issue_id)
where xx_note.created_date is not null;
date_start | created_date
----------------+---------------------
2006-03-17 | 1900-01-01
(...many... rows)
I need to copy date_start to created_date, for all records that meet a
particular where condition.
-Bryce Nesbitt
--
----
Visit http://www.obviously.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to copy data between joined columns?
2006-04-07 20:32 How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
@ 2006-04-07 20:34 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-04-07 21:36 ` Re: How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2006-04-07 20:34 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
On Fri, 2006-04-07 at 15:32, Bryce Nesbitt wrote:
> I have a need to copy/update data from one column to another, based on a
> join condition. Is this easy to do in pure SQL? I have google'ed
> without luck for a easy solution (that's not Microsoft specific, that
> is). For example:
>
> postgres=#
> select xx_thing.date_start,xx_note.created_date
> from xx_thing
> join xx_issue using (thing_id) join xx_note using (issue_id)
> where xx_note.created_date is not null;
>
> date_start | created_date
> ----------------+---------------------
> 2006-03-17 | 1900-01-01
> (...many... rows)
>
> I need to copy date_start to created_date, for all records that meet a
> particular where condition.
If it's for the same rows in the same table, you're overworking it.
update table set field1=field2 where somecondition;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to copy data between joined columns?
2006-04-07 20:32 How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 20:34 ` Re: How to copy data between joined columns? Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-04-07 21:36 ` Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 21:54 ` Re: How to copy data between joined columns? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Bryce Nesbitt @ 2006-04-07 21:36 UTC (permalink / raw)
To: pgsql-sql
Scott Marlowe wrote:
> If it's for the same rows in the same table, you're overworking it.
>
> update table set field1=field2 where somecondition;
>
If it were for the same rows in the same table, I would not have asked.
This is for columns from two tables, whos relationship is only
meaningful after a join.
-Bryce
--
----
Visit <a href="http://www.obviously.com/recycle/">www.obviously.com</a;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to copy data between joined columns?
2006-04-07 20:32 How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 20:34 ` Re: How to copy data between joined columns? Scott Marlowe <smarlowe@g2switchworks.com>
2006-04-07 21:36 ` Re: How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
@ 2006-04-07 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-07 22:24 ` Re: How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-04-07 21:54 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
Bryce Nesbitt <bryce1@obviously.com> writes:
> If it were for the same rows in the same table, I would not have asked.
> This is for columns from two tables, whos relationship is only
> meaningful after a join.
You have to write out the join condition longhand, eg
UPDATE targettable SET targcol = srccol
FROM srctable
WHERE joincol1 = joincol2;
This can be tricky, particularly if there might be more than one source
row joining to a given target row. (IIRC, the effective behavior is
that any given target row's update will occur against a random one of
the possible join partners.) A lot of people prefer to code it using
sub-selects.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to copy data between joined columns?
2006-04-07 20:32 How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 20:34 ` Re: How to copy data between joined columns? Scott Marlowe <smarlowe@g2switchworks.com>
2006-04-07 21:36 ` Re: How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 21:54 ` Re: How to copy data between joined columns? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-07 22:24 ` Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bryce Nesbitt @ 2006-04-07 22:24 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane wrote:
<blockquote cite="mid17020.1144446846@sss.pgh.pa.us" type="cite">
<pre wrap="">Bryce Nesbitt <a class="moz-txt-link-rfc2396E" href="mailto:bryce1@obviously.com"><bryce1@obviously.com></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">If it were for the same rows in the same table, I would not have asked.
This is for columns from two tables, whos relationship is only
meaningful after a join.
</pre>
</blockquote>
<pre wrap=""><!---->
You have to write out the join condition longhand, eg
UPDATE targettable SET targcol = srccol
FROM srctable
WHERE joincol1 = joincol2;
This can be tricky, particularly if there might be more than one source
row joining to a given target row. (IIRC, the effective behavior is
that any given target row's update will occur against a random one of
the possible join partners.) A lot of people prefer to code it using
sub-selects.
</pre>
</blockquote>
That's great, and will work for me. But I do have the multiple match
issue. Can you also give a sub-select example for the archives?
Something robust that works against any combination of join partners
would be really useful.<br>
<br>
<pre class="moz-signature" cols="60">--
----
Visit <a href="http://www.obviously.com/">http://www.obviously.com/</a;
</pre>
</body>
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-07 22:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-07 20:32 How to copy data between joined columns? Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 20:34 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-04-07 21:36 ` Bryce Nesbitt <bryce1@obviously.com>
2006-04-07 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-07 22:24 ` Bryce Nesbitt <bryce1@obviously.com>
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