Oracle SQL: Need help updating a record based on a sum of values in child tables

Let开发者_运维技巧's says I have a set of tables that match the following scenario:

Crates have boxes Boxes have buckets Buckets hold a numeric value called Pieces A crate holds a numeric value called TotalPieces that is the sum of all the pieces in all the buckets in all the boxes within it

So I have the following tables called CRATES, BOXES, BUCKETS. I need an update statement that will populate the TotalPieces column (not shown in the SQL below) of ALL crates in the crates table to be the sum of all the bucket pieces. Yes, I have to do it this way for a specific reason in case anyone decides to be all like, "Well, why are you doing it like that instead of just running a select statement?" I can do the select statement and it looks like this:

SELECT CR.ID, SUM(BU.PIECES) FROM CRATES CR
JOIN BOXES BO ON BO.CRATE_ID = CR.ID
JOIN BUCKETS BU ON POPR.BO_ID = BO.ID
GROUP BY CR.ID;

How do I get the total pieces of all the buckets into the pieces column in the crates table?

This does the trick:

update crates cr set cr.total_pieces = 
(
    select sum(bu.pieces) pieces from crates cr
    join boxes bo on bo.crate_id = cr.id
    join buckets bu on bu.box_id = box.id
    where crate.id = cr.id
    group by crate.id
);

Try this:

 SELECT BO.CRATE_ID AS CRATEID, SUM(BUCKETSUM) AS CRATESUM
  FROM BOXES BO INNER JOIN 
    (SELECT BU.BO_ID AS BOXID, SUM(BU.PIECES) as BUCKETSUM
     FROM BUCKETS BU GROUP BY BU.BO_ID) BU
    ON BU.BOXID = BO.ID
  GROUP BY BO.CRATE_ID