Mysql errors while trying to 'insert into select on duplicate key'

Question

What I want to do is a classic insert or update case but with a 'select' instead of 'values'. I don't think the exact database structure matters in this case, suppose all fields I use in the example are integers. I try to run 2 cases but they both yield errors:

INSERT INTO table1 (a, b, c) 
    SELECT x, SUM(y), COUNT(z)
    FROM table2
ON DUPLICATE KEY UPDATE b = b + x;

this yields

Error Code: 1054. Unknown column 'x' in 'field list'

The second case:

INSERT INTO table1 (a, b, c) 
    SELECT x, SUM(y), COUNT(z)
    FROM table2
ON DUPLICATE KEY UPDATE b = b + count(z);

this yields

Error Code: 1111. Invalid use of group function

In both cases if I remove the duplicate key statement everything works. Also, in both cases the value I want to use in updating b from table 1 is the value from the row in the 'select' where duplicate key occurred. I'm guessing it has to do with the aggregate functions used but otherwise I'm at a loss. Inserting GROUP BY statements did not help in any way.

Gordon Linoff · Accepted Answer · 2015-06-03 16:16:57Z

2

Use the VALUES() function:

INSERT INTO table1 (a, b, c) 
    SELECT x, SUM(y), COUNT(z)
    FROM table2
ON DUPLICATE KEY UPDATE b = b + VALUES(a);

The second case would use VALUES(c) rather than VALUES(a).

answered Jun 3, 2015 at 16:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Vee6 Over a year ago

Thanks for the answer. However if I would want to continue referencing the values down the stack, for example: INSERT INTO table1 (a, b, c) SELECT x, SUM(y), COUNT(z) FROM table2 ON DUPLICATE KEY UPDATE b = b + (SELECT COUNT(x) FROM table2 WHERE -- some condition involving VALUES(a); it doesn't seem to hold. Any idea why?

Gordon Linoff Over a year ago

@Vee6 . . . Gosh, I've never tried subqueries in the on duplicate key expression. You can probably incorporate that into the main select somehow.

Collectives™ on Stack Overflow

Mysql errors while trying to 'insert into select on duplicate key'

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related