Обсуждение: 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>