Juan, i found you a link explaining the access and sqlserver ansi inner joins in update. it's in german, but can be read (i don't speak german :o)) :
http://www.sql-und-xml.de/sql-tutorial/update-aktualisieren-der-zeilen.html but i've never tried this with mysql. there are some other methods, but since view come just in v5, inline views (called subqueries) will certainly be more possible in next versions of mysql (even when updating). Mathias Selon Juan Pedro Reyes Molina <[EMAIL PROTECTED]>: > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
