MySQL Forums
Forum List  »  Newbie

How should I initialize a header field from the sum of a detail field?
Posted by: James Lampert
Date: October 05, 2023 05:05PM

I have two tables in a schema, "invoices" and "invoicedetails," functioning (not surprising, given the names) as a header-and-detail structure. Everything is sitting on a cloud server.

The "invoices" table has fields (relevant to the discussion) "historySequenceNumber" (long established, and unique) and "itemSalesAmount" (just added, and yet to be initialized. This table currently contains 101298 records.

The "invoicedetails" table has fields (again, just the ones relevant to the discussion) "historySequenceNumber" (used to tie the details to the correct header), "itemNumber" (the alphanumeric part number), and "totalLineAmount" (the extended price). This table currently contains 478961 records.

I want to initialize invoices.itemSalesAmount to the sum of invoicedetails.totalLineAmount, for all records in which (1) invoicedetails.historySequencNumber = invoices.historySequenceNumber, AND (2) invoicedetails.itemNumber begins with neither a slash ('/') nor an ampersand ('&').

Wanting to test my logic safely, I connected to the database with SequelPro, and tried:

"select, sum(totalLineAmount) from invoices join invoicedetails on invoices.historySequenceNumber = invoicedetails.historySequenceNumber where substring(invoicedetails.itemNumber, 1, 1) <> '/' and substring(invoicedetails.itemNumber, 1, 1) <> '&'"

This caused that SequelPro window to become unresponsive (another window into the same schema is fine). It's still locked up, the "Stop Query" button didn't do much, and it appears that the only way to even close the window would be to force-quit SequelPro. So obviously my approach was a bad idea.

So what would be the *right* way to do this?

Options: ReplyQuote

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.