From: Phil (freedc.bokgmail.com)
Date: Wed Jul 09 2008 - 15:30:14 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <anandklgmail.com> wrote:

> you should say "group by old.x" and not "old.a"
>
>
> On 7/9/08, Arthur Fuller <fuller.artfulgmail.com> wrote:
>>
>> I think that you need to select old.a otherwise you cannot group by it.
>>
>> Arthur
>>
>> On 7/9/08, Phil <freedc.bokgmail.com> 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 !

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]