Thread: How to copy data between joined columns?
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/
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;
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>
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
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><blockquotetype="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 alsogive a sub-select example for the archives? Something robust that works against any combination of join partners wouldbe really useful.<br /><br /><pre class="moz-signature" cols="60">-- ---- Visit <a href="http://www.obviously.com/">http://www.obviously.com/</a> </pre>