Mysql Update using value from select

Question

I have a weather app, and I'm storing meteo data every hour in a database.

Recently i discovered a tiny bug which lead to the writing of few hundreds faulty records.

To fix the issue i decided to update all the faulty record using the average values for that given day.

I have the following 3 queries to retrieve the value I need:

select TRUNCATE( ( sum(temperature) / count(*) ), 2) as myTempAvg  from MY_TABLE where rawData not in ('chron', 'fallback') and DATE(transmissionDate) = DATE('2019-01-27');
select TRUNCATE( ( sum(humidity) / count(*) ), 2) as myHumyAvg  from MY_TABLE where rawData not in ('chron', 'fallback') and DATE(transmissionDate) = DATE('2019-01-27');
select TRUNCATE( ( sum(pressure) / count(*) ), 0) as myPressAvg  from MY_TABLE where rawData not in ('chron', 'fallback') and DATE(transmissionDate) = DATE('2019-01-27');

I'm trying to figure out a way to use the above query in an UPDATE statement. Something like:

update MY_TABLE set temperature = $QUERY_1_RESULT , humidity = $QUERY_2_RESULT , pressure = $QUERY_3_RESULT where rawData in ('chron', 'fallback')  and DATE(transmissionDate) = DATE('2019-01-27');

Note: $QUERY variables are explaining purpose placeholders.

any idea?

score 1 · Accepted Answer · answered May 03 '19 at 09:29

You can try below -

update MY_TABLE 
(
select TRUNCATE( ( sum(temperature) / count(*) ), 2) as myTempAvg ,
TRUNCATE( ( sum(humidity) / count(*) ), 2) as myHumyAvg,
TRUNCATE( ( sum(pressure) / count(*) ), 0) as myPressAvg
from MY_TABLE 
where rawData not in ('chron', 'fallback') and DATE(transmissionDate) = DATE('2019-01-27')
)as src
set temperature=myTempAvg ,humidity = myHumyAvg , pressure = myPressAvg
where rawData in ('chron', 'fallback')  and DATE(transmissionDate) = DATE('2019-01-27')

comma missing after MY_TABLE, anyway It works. Thanks! – Robdll May 03 '19 at 09:33 — Robdll, May 03 '19 at 09:33

score 0 · Answer 2 · answered May 03 '19 at 09:29

You should be able to do it similarly to the below.

INSERT INTO MY_TABLE (temperature)
SELECT TRUNCATE( ( sum(temperature) / count(*) ), 2) as myTempAvg  
FROM MY_TABLE 
WHERE rawData not in ('chron', 'fallback')
AND DATE(transmissionDate) = DATE('2019-01-27');

Mysql Update using value from select

2 Answers2