Mysql INSERT INTO...SELECT.... ON DUPLICATE KEY UPDATE

Question

I have this insert into select duplicate key update query but after executing it twice, the amount of rows get doubled. Stack overflow wants me to add text, so I'm just writing stuff at this moment because I dont think you guys need more explanation as this query is quite self explanatory.

EDIT 1: For clarification: it should not get doubled, I want the data to get updated.

EDIT 2: Table Info

CREATE TABLE `Distance` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `distanceType_myId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `common_id` int(11) NOT NULL,
   `distance` double NOT NULL,
    PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=786421 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

EDIT 3: Updated table info

CREATE TABLE `Distance` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `distanceType_myId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `common_id` int(11) NOT NULL,
   `distance` double NOT NULL,
   PRIMARY KEY (`id`),
   KEY `uniqueId` (`distanceType_myId`,`common_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=786421 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Actual Query:

INSERT INTO Distance (common_id, distance, distanceType_myId)
SELECT 
c.id, 
AVG(
  ROUND(
    (
      6371 * acos(
        cos(
          radians(p.lat)
        ) * cos(
          radians(a.lat)
        ) * cos(
          radians(a.lng) - radians(p.lng)
        ) + sin(
          radians(p.lat)
        ) * sin(
          radians(a.lat)
        )
      )
    )* 1000
  )
) AS distance, 
'culturel' AS distanceType 
FROM 
immobilier_ad_blank AS c 
LEFT JOIN Adresse AS a ON c.adresse_id = a.id 
JOIN POI AS p on p.discr = 'Culturel' 
AND p.lat IS NOT NULL 
AND p.lng IS NOT NULL 
AND p.lat != '' 
AND p.lng != '' 
AND p.lat != 'Latitude' 
AND p.lng != 'Longitude' 
WHERE 
ROUND(
  (
    6371 * acos(
      cos(
        radians(p.lat)
      ) * cos(
        radians(a.lat)
      ) * cos(
        radians(a.lng) - radians(p.lng)
      ) + sin(
        radians(p.lat)
      ) * sin(
        radians(a.lat)
      )
    )
  )* 1000
)< 5000 
AND a.lat IS NOT NULL 
AND a.lng IS NOT NULL 
AND a.lat != '' 
AND a.lng != '' 
AND a.lat != 'Latitude' 
AND a.lng != 'Longitude' 
GROUP BY 
c.id 
ON DUPLICATE KEY 
UPDATE 
common_id = VALUES(common_id),
distanceType_myId = VALUES(distanceType_myId)

Answer 1

Simple, you don't have a unique key. In your table you need a unique identifier or a natural key as opposed to a surrogate one ( primary key ).

Primary keys have no relation to the data, they are surrogate keys. For example you cant look at a row and know that the primary key for it is one without having the primary key.

A natural key is like a name in a database of only names, or a phone number in a database of phone numbers, it could be a state in a database of states. You wouldn't have two entries for Michigan, or Ohio, for example they are unique by their own right.

Surrogate keys are nice for relationships or use as foreign keys because they are not related to the data. A bad example would be if suddenly the US decided to add a letter to the state's abbreviation. You would have to update all your relationships. In other words you could change the state record and not have to change any other data in your database, because the surrogate key is not related to the data. ( a better example would be making North and South Michigan )

So each has there own use and reason for being.

UPDATE

INSERT INTO
    Distance (common_id, distance, distanceType_myId)
    VALUES ( 1,20.0,'culturel' ) 
    ON DUPLICATE KEY UPDATE 
    id = LAST_INSERT_ID( id ),
    common_id = VALUES( common_id ),
    distance = VALUES( distance ),
    distanceType_myId = VALUES( distanceType_myId )

This is how I did it in the past reading some old code, then it updates the insert id as well.

UPDATE1.1 However, all that said I have found in my testing that if you use PDO with exceptions you can improve insert performance by just ignoring the error. That is if you don't actually want to update the row, or need the id. For example if you just are inserting and don't want duplicates.( code for example only not tested )

 try{
     $PDO->query( "INSERT INTO
    Distance (common_id, distance, distanceType_myId)
    VALUES ( 1,20.0,'culturel' )" );
 }catch( PDOException $e){
     if($e->getCode() != 24000 ){ //not 100% sure of the code number
        //rethrow if not duplicate key
        throw new PDOException( $e->getMessage(), $e->getCode(), $e );
     }
 }

Answer 2

You're missing a unique index - since the only unique key in your table is the auto incrementing id field, you'll need to add a UNIQUE constraint that will be triggered by your INSERT statement.