Awesome! Thanks Baron, works perfectly..
Phil On Tue, Feb 26, 2008 at 10:06 PM, Baron Schwartz <[EMAIL PROTECTED]> wrote: > Hi! > > On Tue, Feb 26, 2008 at 7:04 PM, Phil <[EMAIL PROTECTED]> wrote: > > I have a table countrystats defined as > > > > CREATE TABLE IF NOT EXISTS `countrystats` ( > > `proj` char(6) NOT NULL default '', > > `country` char(50) NOT NULL default '', > > `score` double default NULL, > > `nusers` int(11) default NULL, > > `RAC` double default NULL, > > `last_update` double default NULL, > > PRIMARY KEY (`proj`,`country`) > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > > > To get the data I can do the following > > > > select 'rsi',country,sum(metric1) as total,count(*),sum(metric2) from > > user_table group by country; > > > > This works fine, but then I tried to populate the table with > > > > INSERT INTO countrystats (select 'rsi', country,sum(metric1) as > > total,count(*) as count,sum(metric2) as sumrac,0 from user_table group > by > > country) on duplicate key update last_update=total - > > score,score=total,nusers=count,RAC=sumrac; > > > > which gives me > > > > ERROR 1054 (42S22): Unknown column 'total' in 'field list' > > > > now the insert on it's own without the on duplicate works just fine.. > so why > > does the update not like the 'named' column?? > > > > Any ideas? Can it be done in a single statement? > > That is an odd error. But you can work around it this way: > > INSERT ... > SELECT * FROM ( > SELECT... GROUP BY > ) AS derived_table > ON DUPLICATE KEY .... >
