Re: update, select and join in one statement ?

On Sat, 28 Aug 1999 23:48:32 +0200, "Guenter Gall" <gallg_at_t-online.de> wrote:

a small change lest table2 not contain a match for all rows in table1

update table1 t1
set t1.columnA=(select t2.columnA from table2 t2

                             where t1.columnB=t2.columnB)
WHERE EXISTS (select t2.columnA from table2 t2
                             where t1.columnB=t2.columnB);

Without the exists clause: Every row in table1 will be updated. Where there is no matching row in table2 you will get nulls written into table1 (or an error if the column does not allow nulls).

Gene Hubert
SkillsPoint.com
Morrisville, NC

>Hello Michael,
>
>try this:
>
>update table1 t1
>set t1.columnA=(select t2.columnA from table2 t2
> where t1.columnB=t2.columnB);
>
>Guenter Gall
>gallg_at_t-online.de
>
>
>Michael Keppler <Michael.Keppler_at_bigfoot.com> schrieb in im Newsbeitrag:
>7q9e6p$1h7_at_TGZ3...
>> Hello everybody !
>>
>> I try to build a statement like this:
>>
>> update table1
>> set table1.columnA=table2.columnA
>> where table1.columnB=table2.columnB
>>
>> Is something like this possible? I just don't get it right.
>> What I want is to "copy" the information of table2.columnA into
>> table1.columnA, but only if there is a join on the columns B possible
>> for the row that is to be updated.
>>
>> --
>> Michael Keppler, MCSE
>> IT logic GbR
>> Michael.Keppler_at_gmx.de
>>
>>
>
>
Received on Sun Aug 29 1999 - 13:39:03 CDT