Re: Update a table with another

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sat, 8 Oct 2005 05:18:21 -0400
Message-ID: <AJqdnX4Hidx_E9renZ2dnUVZ_tKdnZ2d@comcast.com>

"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 Received on Sat Oct 08 2005 - 04:18:21 CDT