Re: Nested update?

[Diwakar Bhatt <diwakar.v.bhatt@AC.COM>]

Update equipment a
 set meterreading = (select meterreading from equipment b
                                          where b.eqnum = a.parent)
where ....clause needed to prevent updating the parents


Chuck King <Chuck.L.King@SEALEDAIR.COM>
06/01/99 08:22 PM GMT
Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Diwakar V. Bhatt)
Subject:  Re: Nested update?




I may not have explained that all columns are in the same table and
that their is a parent equipment number attached to each equipment
number. (example:  004000000 is the parent of 00401000)

The table name is equipment and the columns are eqnum(equipment
number), parent(parent equipment number), and meterreading(meter reading)  or

equipment.eqnum
equipment.parent
equipment.meterreading

How would you approach it with this clarification?

Chuck L. King
----------
From: Adam.Dray
To: SQR-USERS
Subject: Re: Nested update?
Date: Tuesday, June 01, 1999 12:37PM

[Chuck King <Chuck.L.King@sealedair.com> asks how to update child
 records using parent data.]


UPDATE child_table
  FROM parent_table p, child_table c
  SET meter_reading = p.meter_reading
  WHERE c.join_column = p.join_column

Of course, your WHERE clause may have to be more complicated. I'm
not sure how the parent-child relation is set up. If it's denormalized,
you may have to do something tricky in there to get the join to work.
You basically want to join each child record to its parent record.
Note that parent_table and child_table can be the same table, as long
as they have different aliases (e.g., p and c).

Adam Dray