Hu Juan, see my answer above "Re: Subselect in an Update query". You can't update and select in a sybquery using the same table "master".
use tempo table for the join and update after. Mathias Selon Juan Pedro Reyes Molina <[EMAIL PROTECTED]>: > > > I'm running mysql 4.1.7. > > For the sake of this message I have created this tables: > > CREATE TABLE `log` ( > `ID` int(11) NOT NULL auto_increment, > `Cod_P` varchar(5) NOT NULL default '', > `Import` double NOT NULL default '0', > PRIMARY KEY (`ID`) > ) ENGINE=MyISAM; > > CREATE TABLE `master` ( > `Cod_P` varchar(5) NOT NULL default '', > `Total` double NOT NULL default '0', > PRIMARY KEY (`Cod_P`) > ) ENGINE=MyISAM; > > I want master.Total to hold a sum of log.Import for every Cod_P > > In Microsoft Sql Server I have run: > > update master > set Total = TotalImport > from ( select sum(Import) TotalImport, log.Cod_P > from master inner join log > on (master.Cod_P=log.Cod_P) > group by log.Cod_P) t > where master.Cod_P = t.Cod_P > > which, IMHO, is a clean way to do this. > > in mysql I get a syntax error. > > I tried > > update `master`,`log` > set Total = Total + Import > where `master`.Cod_P=`log`.Cod_P > > but this only put in master table the value of just one row of every > Cod_P in log table. > > this way it works > > update `master` > set Total = (select sum(Import) from `log` where > `master`.Cod_P=`log`.Cod_P) > > but I get 1 warning (I don't know what warning) and I deem this way > inefficient in case I have to summarize several columns. I would need to > write > > update `master` > set Total = (select sum(Import) from `log` where > `master`.Cod_P=`log`.Cod_P), > set Total1 = (select sum(Import1) from `log` where > `master`.Cod_P=`log`.Cod_P), > set Total2 = (select sum(Import2) from `log` where > `master`.Cod_P=`log`.Cod_P), > set Total3 = (select sum(Import3) from `log` where > `master`.Cod_P=`log`.Cod_P), > set Total4 = (select sum(Import4) from `log` where > `master`.Cod_P=`log`.Cod_P), > set Total5 = (select sum(Import5) from `log` where > `master`.Cod_P=`log`.Cod_P), > set Total6 = (select sum(Import6) from `log` where > `master`.Cod_P=`log`.Cod_P) > > while in Sql Server it would look something like: > > update master > set Total = TotalImport,Total1 = TotalImport1,Total2 = > TotalImport2,Total3 = TotalImport3,Total4 = TotalImport4,Total5 = > TotalImport5,Total6 = TotalImport6 > from ( select sum(Import) TotalImport,sum(Import1) > TotalImport1,sum(Import2) TotalImport2,sum(Import3) > TotalImport3,sum(Import4) TotalImport4,sum(Import5) > TotalImport5,sum(Import6) TotalImport6, log.Cod_P > from master inner join log > on (master.Cod_P=log.Cod_P) > group by log.Cod_P) t > where master.Cod_P = t.Cod_P > > with only one subselect to put your eyes on (and to debug just in case). > > In an example I run, I think mysql fired every one subselect while sql > server do all the work with just one round (it was just one quick test, > so I cannot be sure) > > Maybe I'm overlooking something. > Is there anyone out there with a better mysql syntax to accomplish this? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
