Hello,

I'm having a difficult time finding the right sql syntax to perform an
update. Here is the situation:

I have two tables, each with an orderid field and a removal_date field.
There is a one-to-many relationship between table A and table B, with B
having multiple records to each one in table A, related by the orderid
field. Table A's primary key is the orderid field, and is the only table
that has data in the removal_date field. I would like to update the
removal_date field in table B with the values of the removal_date field in
table A.

Can this be done with a single sql statement? Right now I'm using a VB
program to build the update sql, but this is cumbersome. Any help would be
appreciated.

Thanks.

You would need to check this, but I think it would work.

update TableB
set TableB.removal_date = TableA.removal_date
from Tableb join TableA on TableB.orderid = TableA.orderid


"George J" <gjewell@houston.rr.com> wrote in message
news:Sxn6d.31407$W21.29433@fe2.texas.rr.com...
> Hello,
>
> I'm having a difficult time finding the right sql syntax to perform an
> update. Here is the situation:
>
> I have two tables, each with an orderid field and a removal_date field.
> There is a one-to-many relationship between table A and table B, with B
> having multiple records to each one in table A, related by the orderid
> field. Table A's primary key is the orderid field, and is the only table
> that has data in the removal_date field. I would like to update the
> removal_date field in table B with the values of the removal_date field in
> table A.
>
> Can this be done with a single sql statement? Right now I'm using a VB
> program to build the update sql, but this is cumbersome. Any help would be
> appreciated.
>
> Thanks.
>
>

On Wed, 29 Sep 2004 00:42:26 GMT, George J wrote:

>Hello,
>
>I'm having a difficult time finding the right sql syntax to perform an
>update. Here is the situation:
>
>I have two tables, each with an orderid field and a removal_date field.
>There is a one-to-many relationship between table A and table B, with B
>having multiple records to each one in table A, related by the orderid
>field. Table A's primary key is the orderid field, and is the only table
>that has data in the removal_date field. I would like to update the
>removal_date field in table B with the values of the removal_date field in
>table A.
>
>Can this be done with a single sql statement? Right now I'm using a VB
>program to build the update sql, but this is cumbersome. Any help would be
>appreciated.
>
>Thanks.
>

Hi George,

As an alternative to Oscar's suggestion:

UPDATE tableB
SET removal_date = (SELECT removal_date
FROM tableA
WHERE tableA.orderid = tableB.orderid)


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)