MySQL Forums
Forum List  »  General

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

Options: ReplyQuote


Subject
Written By
Posted
Help with "ON DUPLICATE KEY UPDATE"
June 17, 2013 10:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.