Updating one table from another
We first add a column for the new data: On to the next example, we are given an excel spreadsheet with detailed sales data.Rather than a summary for each store, this table contains all the transactions for all the stores. We must now aggregate the sales data for each store, and update the sales column in our Stores spatial layer with a sum of sales for each store.I was processing 1.2 million rows and killed the process after 3 hours -- it had inserted nothing.I'm concerned that the cursor is making it take so long, but I can't think of any other way to do this. Does Oracle select the data to define the cursor and hold it in memory?
We first use the Virtual XL support to add a virtual table “sales_details”, then: Again, the Stores table, being updated, appears in the WHERE part of the subquery, but not in the FROM clause. And in the SELECT subquery we use SUM(sales) to aggregate sales for each store.
So the Stores table will now drop down to the subquery.
We’ll again use the SUM() function, and our WHERE clause utilizes the ST_Within() function to find which stores are inside each county, and total up sales for those stores.
I am updating a table (m8054_summary) based on data from another table (temp_summary).
I am using a cursor to read temp_summary so that I can process one row at a time.