SQL - IF EXISTS UPDATE ELSE INSERT

<>

This question already has an answer here:

Answers


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 = .....

Need Your Help

jQuery's .css() implementation

javascript jquery css internet-explorer

I was looking through the jQuery code and found this line:

regex assistance for validating an email address

php regex

I am trying to validate an email field. I took this regex from somewhere on here for and I used it on another form I made and it works fine. Yet when I use it now its not matching.