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]
