SQL - IF EXISTS UPDATE ELSE INSERT<>
This question already has an answer here:
If you have a UNIQUE constraint on (deviceID, datum), then you can use INSERT ... ON DUPLICATE KEY
INSERT INTO distance (deviceID, datum, tavolsag) SELECT d.deviceID , DATE(FROM_UNIXTIME(d.timestamp)) as datum , SUM(CASE WHEN d.distanceKM/1000<416 THEN d.distanceKM/1000 ELSE 0 END ) AS tavolsag FROM eventdata d GROUP BY datum, deviceID ON DUPLICATE KEY UPDATE tavolsag = VALUES(tavolsag)
When this statement is executed, this will attempt the insert, and if the inserting would violate a unique key constraint, rather than throw an error, MySQL will execute an UPDATE instead, equivalent to:
UPDATE distance SET tavolsag = ? WHERE device_id = ? AND datum = ?
If there are multiple unique constraints, then the action depends on which unique constraint is flagged as being violated. The actual update that is performed will be relative to the unique constraint that would have thrown the error.
try this :
INSERT INTO distance (deviceID,datum,tavolsag) SELECT deviceID,datum,tavolsag from(select deviceID, DATE(FROM_UNIXTIME(timestamp)) as datum, sum(case when distanceKM/1000<416 then distanceKM/1000 else 0 end) as tavolsag FROM eventdata GROUP BY datum,deviceID)t on duplicate key update set datum=......, tavolsag = .....