How to summarize a table?
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]
Need help in storing and retreving images
__ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help in stroing and retreving images from database
Sir , Iam working on Mysql5.0 version. Can I store .jpeg or .png or .gif etc imagefiles in database. If so where can I get the tutorials or sample code to insert and retrive the images from mysql database. Any help is welcome. Thanking you , Sreedhar __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Unable to install mysql - Bayesian Filter detected spam
Hi yannick,
Not sure if you have found the problem yet, any way there are many tutorials
online which explain how to proberly setup user accounts.
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
yannick
etc. are all separate users with different rights. So the combination of
username, hostname and password is to be thought of as a set.
have alook at below links, which I hope help you kichstart and troubleshoot
any issues you are having.
http://www.devshed.com/c/b/MySQL/
http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables/
http://www.php-mysql-tutorial.com/mysql-tutorial/add-new-mysql-user.php
I hope that you find this info useful.
Best regards
Nils Valentin
Tokyo / Japan
http;//www.be-known-online.com
On Tuesday 14 June 2005 06:58, Yannick wrote:
Kevin,
In addition to that, the ZORUM database works because when I stop mysql,
the following site stops working :
http://www.wxy.nl/zorum_3_5/ with the database ZORUM
Here PHPadmin doesn't give me any privilege to create anything :
http://www.wxy.nl/phpMyAdmin/
I beleive I'm not to far from having it working ut I still have this
priviledge issue.
BEst regards
Yannick
-Message d'origine-
De : Yannick [mailto:[EMAIL PROTECTED]
Envoy : Monday, June 13, 2005 11:54 PM
: [EMAIL PROTECTED]
Cc : mysql@lists.mysql.com
Objet : RE: [SPAM] - Unable to install mysql - Bayesian Filter detected
spam
Kevin,
Thanks for your answer. See below the results :
* I' really wondering if there is not any missing files.
* The mysql.soc file is complettly empty
* I can only access myssql when I am not in root.
* I can only see 1 database test when I know there is others like zorum
which is working
* The command Grant doesn't work.
The result is that I can never access any database.
I have been folowing the installatin process but it still does not work.
Do you mind to have a look at the details below and advice on which files I
should have a look.
Thanks in advance.
Yannick
fujitsu:/etc # mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/etc # su mysql
[EMAIL PROTECTED]:/etc mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.15-Max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql quit
Bye
[EMAIL PROTECTED]:/etc mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
[EMAIL PROTECTED]:/etc mysql -u mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.15-Max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql quit
Bye
[EMAIL PROTECTED]:/etc su
Password:
fujitsu:/etc # mysql -u mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/etc # mysql -u yannick
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
fujitsu:/etc # su mysql
[EMAIL PROTECTED]:/etc mysql -u yannick
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 4.0.15-Max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql SET PASSWORD FOR ''@'localhost' = PASSWORD('Yannick');
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql SET PASSWORD FOR 'yannick' = PASSWORD('Yannick');
ERROR 1133: Can't find any matching row in the user table
mysql
mysql show databases;
+--+
| Database |
+--+
| test |
+--+
1 row in set (0.00 sec)
mysql
mysql UPDATE mysql.user SET Password = PASSWORD('Yannick')
- ;
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql
fujitsu:/bin # mysqlshow
mysqlshow: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/bin # mysql -e SELECT Host,Db,User FROM db mysql
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
fujitsu:/bin # bin/mysqld_safe --user=mysql --log
bash: bin/mysqld_safe: Aucun fichier ou rpertoire de ce type
[1] 16599
[1] Exit 127bin/mysqld_safe --user=mysql --log
fujitsu:/bin # mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)'
fujitsu:/bin #
mysql SHOW DATABASES;
+--+
| Database |
+--+
| test |
+--+
1 row in set (0.00 sec)
mysql GRANT ALL ON menagerie.* TO 'mysql'@'localhost';
ERROR 1044: Access denied for user: '@localhost' to database 'menagerie'
mysql GRANT ALL ON test TO 'mysql'@'localhost';
ERROR 1046: No Database Selected
mysql select test;
ERROR 1054: Unknown column 'test' in 'field list'
mysql use test
Database changed
mysql GRANT ALL ON test TO 'mysql'@'localhost';
ERROR 1044: Access denied for user: '@localhost' to database 'test'
mysql
-Message d'origine-
De : Kevin
Need help
Isn't MySql supports large amounts of data to be stored in databases. What is the maximum number of records that can be handled or stored in Mysql. Is there any limit. If i want to store large amounts of data then is it necessary to migrate to another database. Please reveal this . Any help is welcome. Thanking you , Sreedjhar. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help
Hi Shreedjhar, Are you aware of this paper ? http://www.fabalabs.org/research/papers/FabalabsResearchPaper-OSDBMS-Eval.pdf It may perfectly answer most of your questions. Best regards Nils valentin Tokyo / Japan http'//www.be-known-online.com On Saturday 18 June 2005 22:01, madderla sreedhar wrote: Isn't MySql supports large amounts of data to be stored in databases. What is the maximum number of records that can be handled or stored in Mysql. Is there any limit. If i want to store large amounts of data then is it necessary to migrate to another database. Please reveal this . Any help is welcome. Thanking you , Sreedjhar. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help
At 09:01 AM 6/18/05, madderla sreedhar wrote: Isn't MySql supports large amounts of data to be stored in databases. What is the maximum number of records that can be handled or stored in Mysql. Is there any limit. If i want to store large amounts of data then is it necessary to migrate to another database. Please reveal this. You could try reading the online manuals: http://dev.mysql.com/doc/mysql/en/index.html 1.4.4. How Big MySQL Tables Can Be 11.5. Column Type Storage Requirements The maximum number of rows is limited only by maximum table size and size of your hard disk. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3G address space and large memory on x86 32bit (was: can innodb_buffer_pool_size be set 2Gb on FreeBSD?)
I'm hoping this will serve as a reference since this topic comes up often. If you Google search, you'll find people who explain these topics better than I do, but here's what you need to know. 2GB is the division set between the user's address space, and the address space the kernel maintains for kernel and other code to be mapped into. It is adjustable, for better or for worse. In FreeBSD, look in /boot/defaults/loader.conf and you'll see kern.maxdsiz. If you have 4GB of ram, you can probably get away with setting this to 3GB. You can expirement with the setting and if your system panics because you set the value too high, you can just use the boot loader to override kern.maxdsiz back to something sane. Before I went 64bit on my MySQL boxes, I ran MySQL under FreeBSD this configuration. For Windows XP Pro (which you should not be using for MySQL or any other server), Windows 2000 Advanced/Datacenter Server, Windows Server 2003 (any edition), or Windows NT 4.0 Enterprise you can specify the /3GB switch which has pretty much the same effect as the FreeBSD tuning, except that you need to recompile MySQL specifying the /LARGEADDRESSAWARE linker flag, or use Editbin.exe (part of Visual Studio 6) to modify the stock MySQL for Windows EXE (probably your best bet). As I understand it, Linux runs 3G user space, 1G kernel space by default. FreeBSD and Windows don't use the 3G/1G split because that's a very small amount of address space for things like your AGP aperture, cache manager, and other kernel usage. Traditionally a 2GB/2GB split works out best for everyone because most programs don't use more than 2GB of memory. However, for MySQL you *may* find that shrinking your disk cache and other kernel structures in order to increase your key cache pay off. If you have an x86 system with more than 4GB of memory, and you have PAE enabled, and you have a kernel (of whatever operating system) that supports PAE, you get more than 4GB of memory total available to *all* of your applications, but not more than 4GB available to any particular application because the address space is still 32bit. PAE just lets your operating system to allocate to processes all of your 6GB or 8GB of physical memory. Under Windows XP/2000/2003, you can use Address Windowing Extensions to access more than 4GB of memory in a single application. Say you had a Dual Xeon 3.0ghz with 16GB of ram. You call VirtualAlloc() to acquire some address space for mapping the memory, call AllocateUserPhysicalPages() to allocate yourself 10GB worth of pages, and MapUserPhysicalPages() to map them into the part of your normal the 32bit space you reserved with VirtualAlloc(). You map the pages in, read/write them, and unmap them. Because the mapping of pages is just tweaking virtual memory table entries, the operation is very fast. AWE will remind some old DOS programmers of EMS, where you paged 16K EMS pages into the 64KB EMS frame between 640K and 1MB of memory. However, the overhead of AWE paging is much faster because there are no mode switches and no memcopy takes place when you map/unmap pages (which may or may not have occured in EMS depending on which EMS implementation you were using). Corrections welcome. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to summarize a table?
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]
Re: need help in stroing and retreving images from database
Hi, all binary docs can be inserted in blob columns. see this link for a php insert method :http://www.phpcs.com/code.aspx?ID=30945 this is mysql doc : http://dev.mysql.com/doc/mysql/en/blob.html Mathias Selon madderla sreedhar [EMAIL PROTECTED]: Sir , Iam working on Mysql5.0 version. Can I store .jpeg or .png or .gif etc imagefiles in database. If so where can I get the tutorials or sample code to insert and retrive the images from mysql database. Any help is welcome. Thanking you , Sreedhar __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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]
Re: Ordinal number within a table
hi, the position of a record depend on the sort order you choose in your queries (generally order by) and the execution plan of them. this execution plan depends on data, indexes, and the query itself. So what you call order is candidate to changing between two selects. the method shown with @row++ is a solution, but the given order is the display data order for you. use an order by so make it stronger. Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Ed Reed Is there way to return the ordinal position of a value within a table? Let's say I have a table of phone numbers. Over time the table has had additions and deletions. The table has an autonumber ID field. If I sort by the ID field I'd like to know what position the number '555-1212' is in the table. I'm not sure why you'd want to know this, but it's generally a good idea to abandon the thought that records in a database are stored in a certain order with a position number attached to them. The internal way of storing data differs from engine to engine and you can never be sure that these internals will not be modified in newer releases of MySQL. It's best to think of a table as a collection of records which can be presented in the way you want. The way the database decides to store the data is in many cases not relevant at all; that's the job of the database. Regards, Jigal. -- 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]
Re: How to summarize a table?
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]
Re: Subselect in an Update query
On 6/17/05, [EMAIL PROTECTED] wrote: There is one caveat: It is not currently possible to modify a table and select from the same table in a subquery. That is not the only problem: there is no guarantee the subquery will only return one record. So even if MySQL wouldn't have this limitation you would still run the risk of an executor error when the subquery returns more then one record. Try this: UPDATE table1 a, table2 b SET a.field1 = b.field1 WHERE b.field2 = 'Some Value' AND a.field2 = 'Another Value' Jochem PS Please use single quotes to delimit strings, sticking to the SQL standard makes it easier to read. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to summarize a table?
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]
Re: simple data GUI editor?
I have been using and would highly recommend SQLyog from http://www.webyog.com Regards, Ritesh --- D_C [EMAIL PROTECTED] wrote: i was wondering if people can recommend a simple Excel like tool for editing data? MySql control center - seems to have limitations (unicode, not in dev anymore) Query browser - have to type raw sql to show/hide columns... ideally i want something with a few more features than either of these, eg list data in a vertical table rather than just horizontal... lookups to other tables but more oriented to lots of interactive editing of the DB data than DB admin. I guess more like an Access GUI... (puts on flame pants) + ideally not very expensive :-) thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple data GUI editor?
Have you tried SQLyog? Somehow I prefer it more then MySQL-Front. Regards, Karam --- Carl [EMAIL PROTECTED] wrote: We use MySQL-Front from Star-Tools GmbH (www.mysqlfront.de)... works pretty much like you have asked. Thanks, Car - Original Message - From: Berman, Mikhail [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: D_C [EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:36 PM Subject: RE: simple data GUI editor? Well, Actually MS-Access through ODBC should work for you -Original Message- From: D_C [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 4:28 PM To: mysql@lists.mysql.com Subject: simple data GUI editor? i was wondering if people can recommend a simple Excel like tool for editing data? MySql control center - seems to have limitations (unicode, not in dev anymore) Query browser - have to type raw sql to show/hide columns... ideally i want something with a few more features than either of these, eg list data in a vertical table rather than just horizontal... lookups to other tables but more oriented to lots of interactive editing of the DB data than DB admin. I guess more like an Access GUI... (puts on flame pants) + ideally not very expensive :-) thanks! -- 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] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.5/18 - Release Date: 6/15/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.5/18 - Release Date: 6/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
