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

what want classic insert or update case 'select' instead of 'values'. don't think exact database structure matters in case, suppose fields use in example integers. try run 2 cases both yield errors:

insert table1 (a, b, c)      select x, sum(y), count(z)     table2 on duplicate key update b = b + x; 

this yields

error code: 1054. unknown column 'x' in 'field list'

the second case:

insert table1 (a, b, c)      select x, sum(y), count(z)     table2 on duplicate key update b = b + count(z); 

this yields

error code: 1111. invalid use of group function

in both cases if remove duplicate key statement works. also, in both cases value want use in updating b table 1 value row in 'select' duplicate key occurred. i'm guessing has aggregate functions used otherwise i'm @ loss. inserting group by statements did not in way.

use values() function:

insert table1 (a, b, c)      select x, sum(y), count(z)     table2 on duplicate key update b = b + values(a); 

the second case use values(c) rather values(a).