Sorry, that was just a typo, should have been
INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by old.x) on duplicate key update b=sum(old.y); but this gives ERROR 1111 (HY000): Invalid use of group function INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group by old.x) works fine... Any ideas ? Phil On Wed, Jul 9, 2008 at 1:07 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > you should say "group by old.x" and not "old.a" > > > On 7/9/08, Arthur Fuller <[EMAIL PROTECTED]> wrote: >> >> I think that you need to select old.a otherwise you cannot group by it. >> >> Arthur >> >> On 7/9/08, Phil <[EMAIL PROTECTED]> wrote: >> > >> > Is it possible to do an insert into with subselect and group by with an >> > additional on duplicate insert ? >> > >> > CREATE TABLE NEW_TABLE ( >> > `a` varchar(10), >> > `b` double >> > ) engine=MyISAM; >> > >> > >> > INSERT INTO NEW_TABLE (select old.x,sum(old.y) from OLD_TABLE old group >> by >> > old.a) >> > on duplicate key >> > update b=sum(old.y); >> > >> > I get invalid group by clause on that. >> > >> > Currently I achieve the same thing using two seperate queries, but >> wondered >> > if I could consolidate as they take ~ 30mins in total (much more complex >> > tables). >> > >> > Anyway, more curious than anything on why it's disallowed and if it's >> just >> > something silly I'm missing. >> > >> > Phil >> > >> > > -- Help build our city at http://free-dc.myminicity.com !
