How should I initialize a header field from the sum of a detail field?
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 invoices.id, 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?