Hi, Mathias!
I had read that post. Just thought it was not the same case. I'm reading
log table and updating master table.
What do you think about Sql Server sintax. Is that sql standard or a sql
server dialect?
Actually I'm working around this using a temp table. I would like to
know if there is a pure sql solution because I don't like to have more
temp tables that strictly needed.
Thanks for your time. Time is our most valuable asset!
[EMAIL PROTECTED] wrote:
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]
