Getting a SUM in an UPDATE

[Josh]

mailing.database.mysql -

I'm trying to update a "parent" table with the sum of data in a "child
table". To illustrate, let's say our database was modeled around a book:

book
-> chapter
-> page
-> attachment (pictures)

The -> represents a one-to-many relationship (e.g. a book has chapters).
Each attachment has a filesize and the parent book has a total_size
(denormalized) column that is the sum of all it's children attachments. I'd
like a query to update total_size in the book table.

Below is what I've come up with thus far. Obviously, it's not working, and
hence my post. The nested SELECT query works fine, but I'm receiving a "You
can't specify target table 'book' for update in FROM clause" error. Please
let me know your thoughts.

update
book
set
total_size = (
select
sum(attachment.filesize)
from
attachment, page, chapter, book
where
attachment.page_id = page.page_id
and
page.chapter_id = chapter.chapter_id
and
chapter.book_id = book.book_id
and
book.book_id = '5'
)
where
book_id = '5'

--
Josh

[Josh]

mailing.database.mysql -

Please ignore post. I've figured it out - I needed to remove "book" from
the inner FROM clause.

update
book
set
book.total_storage = (
select
sum(attachment.filesize)
from
attachment, page, chapter
where
attachment.page_id = page.page_id
and
page.chapter_id = chapter.chapter_id
and
chapter.book_id = book.book_id
and
book.book_id = '5'
)
where
book.book_id = '5'

--
Josh