Track Product Sales On Two Tables-CodePudding

I would like to track product sales with two tables.

The sales in one session and the total sales.

Here is how it would go :

I have a script tracking sales in realtime and inserting new data into the run_data table :

table run data : url: varchar, name: varchar, unit_price: float, total_sales: float, started_tracking: datetime

with a query like

INSERT INTO run_data
(url, name, unit_price, total_sales, started_tracking)
VALUES (%s, %s, %s, %s, %s)
ON DUPLICATE KEY
UPDATE total_sales = %s

My goal is to have two tables, run_data and total_data.

Each time there is a sale, I would update the data in run_data (already done).

And then, sometimes, I would like to reset the run_data, clear the table, and move all data to total_data.

Is there a request in SQL to do this ?

Something like

INSERT INTO total_data 
url, name, unit_price, total_sales, started_tracking)
VALUES (SELECT url, name, unit_price, total_sales as new_sales, started_tracking FROM run_data)
ON DUPLICATE KEY
UPDATE total_sales = total_sales   new_sales

TRUNCATE TABLE run_data

How would you go about doing this?

Thank you :)

CodePudding user response：

To add the new sales to the "old" total_sales, you can use VALUES(total_sales) which has the "new" total_sales value

INSERT INTO total_data 
(url, name, unit_price, total_sales, started_tracking)
SELECT url, name, unit_price, total_sales as new_sales, started_tracking FROM run_data
ON DUPLICATE KEY
UPDATE total_sales = total_sales   VALUES(total_sales)