MYSQL: Transfer values from one table to another based on date/hour

Frechdachs Published at Dev

29

Frechdachs

i have the following MYSQL tables:

Part of the input table 'input':

and part of the Output Table 'output':

both date/time columns are pre given and cannot be changed. they are the basis of the MYSQL code I want to use to do the following: I want to sum all values from 'total' column of the input table from the same date and same hour and put them into 'total' column of the output table where the 'time' column has the same date and same hour as in the input table.

Would this be possible with MYSQL only? Or do I need PHP as well? The input table has about 400,000 values.

I started with:

SELECT SUM(total) FROM input
WHERE date BETWEEN '2019-06-06 00:00:00' AND '2019-06-06 23:59:59'

But I dont know how to continue.

Any help would be appreciated. Thanks in advance :)

forpas

Get the sums in the input table and join to the output table in the UPDATE statement:

update output o 
inner join (
  select date_format(date, '%Y%m%d%H') hour, sum(total) total
  from input
  group by date_format(date, '%Y%m%d%H')
) i on i.hour = date_format(o.date, '%Y%m%d%H')
set o.total = i.total

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-05-23

Comments

0 comments

Login to comment

Related

Transfer from one table to another

How to add a new column in one table based on values from another table in Mysql?

Update multiple columns in one table based on values in another table in mysql

MySQL how to UPDATE a field in one table based on values in another table

MYSQL - sum values of rows from one table based on criteria from another

Map values from one table to another table based on timestamp in postgresQL

Retrieving records from one table based in 'similar' values of another table

add column to one table based on values and conditions from another table

Filling in values from one table to another table based on condition

Update column value of one table based on values from another table

MySQL: Update or Copy data from one table to another based on matching values

mysql update values from one column in same table based on another 3 column value

Update mysql one table from another table based on dates

Filter mysql table based on values from another table

Multiply daily-based values from one table with quarter-based values from another table

Query to Sum values from one table based on values from another table, considering null values

How to transfer values from one dataframe to another?

MySQL - count distinct values from one column based on another column

Transfer data from one table to another on insert

Transfer files from one table to another in impala

mysql insert from one table to another based on select

MySQL JOIN values from another table based on (duplicate) values from original table within that table

MYSQL problem: How to add values from one table to another

MySQL: Is there a way to insert values from one table to another?

How to automatically load values from one table column to another in MYSQL

how to copy only distinct values from one table to another in Mysql?

mysql insert unique values from one column to a column of another table

Oracle SQL: Transfer certain records from one table to another filtering rows based on condition

MYSQL query - simple way to return all values from one column based on a DISTINCT value in another column in the same table?

TOP Ranking

Article

HotTag

Archive