Mark C. Stock schrieb:
> "Andreas Mosmann" <keineemails_at_gmx.de> wrote in message
> news:1128760560.99_at_user.newsoffice.de...
>
>>Hi NG,
>>
>>maybe it is too early in the morning ...
>>
>>I have 2 tables [1]
>>
>>TableA (1000 rows) : ColA, ColB, ColC
>>TableB (100 rows): ColD, ColE, ColF
>>
>>and I want to update TableA for each rows, that TableA.ColA=TableB.ColD
>>There should be 100 updates.
>>
>>It is easy to select these rows
>>
>>select
>> ColA,ColB,ColC,ColE,ColF
>>from
>> TableA
>>join
>> TableB
>>on
>> TableA.ColA=TableB.ColD
>>
>>But in this moment I am unable to find a statement to update these rows in
>>TableA.
>>
>>Thanks in advance
>>Andreas
>>
>>[1] the 100 rows of TableB contain the old values of TableA at the rows
>>specified by ColD/ColA (PK)
>>
>>
>>
>>--
>>wenn email, dann AndreasMosmann <bei> web <punkt> de
>
>
> you need a correlated subquery.
>
> update x
> set y = (select a from b where x.m = y.n)
>
> ++ mcs
>
>
if only rows existing in the join should be updated, You also must use
where exists (select * from b where y.n = x.m)
Received on Sat Oct 08 2005 - 04:28:40 CDT
