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

Question

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`,`ddata`,`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?

+1 for posting the scripts. All do as @Link does!

Quassnoi
– Quassnoi

2011-04-20 10:18:07 +00:00
Commented Apr 20, 2011 at 10:18 — Quassnoi
– Quassnoi, Commented Apr 20, 2011 at 10:18

Quassnoi · Accepted Answer · 2011-04-20 10:22:19Z

1

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?

answered Apr 20, 2011 at 10:22

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Link Over a year ago

I'm not. I made a mistake in copying it from the test database I created. Thank you for pointing it out. The problem I am having is with the second row however. Thanks @Quassnoi

Quassnoi Over a year ago

@Link: I cannot reproduce the problem. Are you sure it's not from the test database as well?

Link Over a year ago

Still not sure what was the problem, but I seemed to have fixed it. Actually the problem doesn't seem like it was with the INSERT SELECT UPDATE code, but I think it was more of a problem with my column references. Always use fully qualified column names . >>

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related