MySQL INSERT ... SELECT... ON DUPLICATE KEY UPDATE increment

I am having some problems with a INSERT SELECT ON DUPLICATE KEY UPDATE query. I want to to perform actions such as increment of a field in the table being inserted on for each of the rows returned by the select, some of which will need to update the same row.

Set Up:

Table source

    CREATE TABLE `source` (
  `key` int(11) NOT NULL AUTO_INCREMENT,
  `data` int(11) DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  `group` int(11) DEFAULT NULL,
  PRIMARY KEY (`key`)
);

Table dest

    CREATE TABLE `dest` (
      `dkey` int(11) NOT NULL,
      `ddata` int(11) DEFAULT NULL,
      `dstate` int(11) NOT NULL,
      PRIMARY KEY (`dkey`,`dstate`)
    ) ;

Source Test Values

INSERT INTO `source` (`key`,`data`,`state`,`group`) VALUES (1,1,1,1);  
INSERT INTO `source` (`key`,`data`,`state`,`group`) VALUES (2,2,2,1);   
INSERT INTO `source` (`key`,`data`,`state`,`group`) VALUES (8,4,2,1);

Query:

INSERT INTO `test`.`dest` (`dkey`,`dda开发者_Python百科ta`,`dstate`)   
    SELECT `group`,`data`,`state` FROM `test`.`source`   
    WHERE `group` = 1  
    ON DUPLICATE KEY UPDATE  
     `ddata`= `ddata`+VALUES(`ddata`);

What I need it to do is when the row does not already exist in dest to create a new row with data = 1. When the row already exists I need it to increment the data.

After executing the above query the results are:

dkey ddata dstate  
1 1 1  
1 4 2   

Where what I want them to be is

1 1 1  
1 6 2   

Instead of adding to the previous value it is replacing it.

Any suggestions?

When reproducing, I get the following results:

1, 1, 1
1, 6, 2

which seems to be right, since you have data = 1 for state = 1 and data = 2, 4 for state = 2 in your source data.

Why are you expecting 2 in the first record?