Newbie update question-- take from one table into another

27 Jul 2006 7:58 PM

Steve Hershoff

I have two tables, TBL_PARENT and TBL_CHILD. TBL_PARENT has a primary key
(call it parentkey), while TBL_CHILD uses it as a foreign key. The tables
share a one-to-many relationship, with TBL_PARENT acting as the one and
TBL_CHILD as the many, as you'd expect.

There is a column in TBL_PARENT I'd like to use in a global update of
TBL_CHILD. Both tables have a column named "benefit" which is of type
char(20). Right now all the useful information from that column is in
TBL_PARENT. My goal is to update the same column in TBL_CHILD with the
parent's information.

Could someone outline a query that will let me do this? I assume a subquery
is involved, but I'm having trouble with the details. Hey, thanks.

27 Jul 2006 8:11 PM

Tom Cooper

First, if your intention is that the benefit column in TBL_CHILD should
always have the value that is in TBL_PARENT, then you should not do this,
instead, just keep the benefit information on the parent, and join to that
table when you need the benefit information for the child.

On the other hand, if your design is such that sometimes that benefit column
in TBL_CHILD will be different from that in the TBL_PARENT row, and this is
something like a one time process to initialize the column in TBL_CHILD,
then you want something like the following untested update - of course,
carefully test this or any other update statements before actually running
them on your production database:

Update c
Set benefit = p.benefit
From TBL_CHILD c
Inner Join TBL_PARENT p On c.parentkey = p.parentkey

Tom

Show quote

"Steve Hershoff" <babba***@nowhere.com> wrote in message
news:%23BmIJcbsGHA.1976@TK2MSFTNGP02.phx.gbl...
>I have two tables, TBL_PARENT and TBL_CHILD. TBL_PARENT has a primary key
> (call it parentkey), while TBL_CHILD uses it as a foreign key. The tables
> share a one-to-many relationship, with TBL_PARENT acting as the one and
> TBL_CHILD as the many, as you'd expect.
>
> There is a column in TBL_PARENT I'd like to use in a global update of
> TBL_CHILD. Both tables have a column named "benefit" which is of type
> char(20). Right now all the useful information from that column is in
> TBL_PARENT. My goal is to update the same column in TBL_CHILD with the
> parent's information.
>
> Could someone outline a query that will let me do this? I assume a
> subquery
> is involved, but I'm having trouble with the details. Hey, thanks.
>
>

[attached file: Query Analyzer.lnk]

27 Jul 2006 8:17 PM

Steve Hershoff

Thanks Tom. Your instinct is correct; we're running this as a one time only
procedure to migrate the data from TBL_PARENT to TBL_CHILD. We'll then
remove the column from TBL_PARENT.

This statement should be just what I need. Appreciate it!

-Steve.

Show quote

"Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message
news:qridnargQLl6gFTZnZ2dnUVZ_q-dnZ2d@comcast.com...
> First, if your intention is that the benefit column in TBL_CHILD should
> always have the value that is in TBL_PARENT, then you should not do this,
> instead, just keep the benefit information on the parent, and join to that
> table when you need the benefit information for the child.
>
> On the other hand, if your design is such that sometimes that benefit
> column in TBL_CHILD will be different from that in the TBL_PARENT row, and
> this is something like a one time process to initialize the column in
> TBL_CHILD, then you want something like the following untested update - of
> course, carefully test this or any other update statements before actually
> running them on your production database:
>
> Update c
> Set benefit = p.benefit
> From TBL_CHILD c
> Inner Join TBL_PARENT p On c.parentkey = p.parentkey
>
> Tom
>

27 Jul 2006 8:13 PM

Vern Rabe

Steve:

This should work:

UPDATE TBL_CHILD
SET benefit = P.benefit
FROM TBL_CHILD C
JOIN TBL_PARENT P
ON C.parentkey = P.parentkey;

But why do you have a benefit column in the child table. Usually you only
denormalize like that for performance reasons, but this doesn't sound like a
situation where poor performance should require denormalizing.

HTH
Vern Rabe

Show quote

"Steve Hershoff" wrote:

> I have two tables, TBL_PARENT and TBL_CHILD. TBL_PARENT has a primary key
> (call it parentkey), while TBL_CHILD uses it as a foreign key. The tables
> share a one-to-many relationship, with TBL_PARENT acting as the one and
> TBL_CHILD as the many, as you'd expect.
>
> There is a column in TBL_PARENT I'd like to use in a global update of
> TBL_CHILD. Both tables have a column named "benefit" which is of type
> char(20). Right now all the useful information from that column is in
> TBL_PARENT. My goal is to update the same column in TBL_CHILD with the
> parent's information.
>
> Could someone outline a query that will let me do this? I assume a subquery
> is involved, but I'm having trouble with the details. Hey, thanks.
>
>
>

27 Jul 2006 11:54 PM

Steve Hershoff

Thanks, Vern. See my comment above as to why we're doing this; we're
basically migrating the data from TBL_PARENT to TBL_CHILD. Otherwise you're
right about it being denormalized and prone to data entry inconsistencies
down the road.

-Steve

Show quote

"Vern Rabe" <VernR***@discussions.microsoft.com> wrote in message
news:D44BE435-F8C9-4D3D-9FAC-939377151D75@microsoft.com...
> Steve:
>
> This should work:
>
> UPDATE TBL_CHILD
> SET benefit = P.benefit
> FROM TBL_CHILD C
> JOIN TBL_PARENT P
> ON C.parentkey = P.parentkey;
>
> But why do you have a benefit column in the child table. Usually you only
> denormalize like that for performance reasons, but this doesn't sound like
> a
> situation where poor performance should require denormalizing.
>
> HTH
> Vern Rabe