Help with "ON DUPLICATE KEY UPDATE"
Posted by:
Luca V
Date: June 17, 2013 10:33AM
Hi,
i have a table with record identified by IDDEVICE, IDCUSTOMER, IDPARK, DATETIME.
Usually i have a record every 5 minutes identified by DATETIME.
I create a copy of the table where i want to group the record by IDDEVICE, IDCUSTOMER, IDPARK,date(datetime) ( 1 record a day and not 1 every 5 minutes ).
I also have the field PLANE DIRECT RADIATION that is grouped by the function SUM.
Sometimes i launch the query at 12:00 and SUM (PLANE DIRECT RADIATION) have i value, but if i launch the query again at 23:00 the record of the day already exist and the value of the SUM(PLANE DIRET RADIATION) is different...how can update that value ?
insert into weather_stations_data_limited
SELECT IDDEVICE, IDCUSTOMER, IDPARK, DATE_ADD(date(datetime), INTERVAL 14 HOUR), now(), avg(`DEVICE TEMPERATURE`),sum(`PLANE DIRECT RADIATION`)
FROM `weather stations data`
where hour(datetime)<22 and hour(datetime)>5 and `PLANE DIRECT RADIATION`> 1
and `LAST UPDATE` >
(SELECT DATE_ADD(max(`LAST UPDATE`),INTERVAL -30 MINUTE) FROM weather_stations_data_limited)
group by idcustomer,idpark,iddevice,date(datetime)
ON DUPLICATE KEY UPDATE
`PLANE DIRECT RADIATION`=sum(`PLANE DIRECT RADIATION`)
last row "`PLANE DIRECT RADIATION`=sum(`PLANE DIRECT RADIATION`)" doesn't work