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 .... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
