Bug: Berkley DB seems to have problems with multi table updates.
Hi, if I try a multi table update with a Berkley DB Table, where the updated table is bigger than the joined table, the system produces the strange Got error 22 from table handler error. The attached (zipped) script should demonstrate the problem. Gru, Thomas Kathmann [EMAIL PROTECTED] attachment: test.zip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: re: re: re: Multi-Table Updates
ajitdixit, Wednesday, November 27, 2002, 7:44:55 PM, you wrote: aasdcdi I had earlier downloaded mysql-4.1.1 development branch source code and had aasdcdi noticed that This bug is also present in 4.1.1 development tree source code Merge of 4.0.6 changes into 4.1 was made several days ago. aasdcdi I had downloaded mysql-4.0 development source code as mentioned in mysql aasdcdi documentation but could not compile it as innodb related libraries were not aasdcdi present in this code -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: re: Multi-Table Updates
ajitdixit, Wednesday, November 27, 2002, 7:04:51 AM, you wrote: aasdcdi Result for Show Grants is as under aasdcdi SQL result aasdcdi Host: localhost aasdcdi SQL-query: SHOW GRANTS FOR aldixit@localhost; aasdcdi Grants for aldixit@localhost aasdcdi GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'aldixit'@'localhost' aasdcdi IDENTIFIED BY PASSWORD 'xx' aasdcdi GRANT SELECT ON `mysql`.* TO 'aldixit'@'localhost' WITH GRANT OPTION aasdcdi GRANT ALL PRIVILEGES ON `samir`.* TO 'aldixit'@'localhost' WITH GRANT OPTION [skip] aasdcdi Multi-Table Update with user aldixit aasdcdi SQL-query : aasdcdi update Stockists, areas set a_nm = aname WHERE area = acd aasdcdi MySQL said: aasdcdi update command denied to user: 'aldixit@localhost' for table 'areas' Thanks for detailed bug report. I could repeat it on 4.0.5, but it works perfectly on 4.0.6. mysql update Stockists, areas set a_nm = aname WHERE area = acd; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 Seems, this bug is already fixed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: re: Multi-Table Updates
Hi , Thanks for reply I had earlier downloaded mysql-4.1.1 development branch source code and had noticed that This bug is also present in 4.1.1 development tree source code I had downloaded mysql-4.0 development source code as mentioned in mysql documentation but could not compile it as innodb related libraries were not present in this code Can you please tell me how to get mysql-4.0.6 source code which can be compiled with innodb support Regards Ajit Dixit - This mail sent through http://shreya.co.in/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-Table Updates
I am using mysql-4.0.5a-beta on Linux Redhat 7.3 server with php-4.3.0RC1 When I connect and execute my quiries as root user my query of multi-table update works fine update Stockists , areas set Stockists.a_nm = areas.aname where Stockists.area=areas.acd Query Executed OK , one row affected The Query results are OK But when I execute the same query with different user having update and lock table previledges I get error update command denied to user: 'aldixit@localhost' for table 'areas' What previledges are required for user to execute multi-table update? Regards Ajit Dixit - This mail sent through http://shreya.co.in/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Multi-Table Updates
ajitdixit, Tuesday, November 26, 2002, 10:33:04 AM, you wrote: aasdcdi I am using mysql-4.0.5a-beta on Linux Redhat 7.3 server with php-4.3.0RC1 aasdcdi When I connect and execute my quiries as root user my query of multi-table aasdcdi update works fine aasdcdi update Stockists , areas set Stockists.a_nm = areas.aname where aasdcdi Stockists.area=areas.acd aasdcdi Query Executed OK , one row affected aasdcdi The Query results are OK aasdcdi But when I execute the same query with different user having update and lock aasdcdi table previledges I get error aasdcdi update command denied to user: 'aldixit@localhost' for table 'areas' aasdcdi What previledges are required for user to execute multi-table update? You must have UPDATE privilege on all tables mentioned in the UPDATE statement. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Multi-Table Updates
I have update priviledge on all the tables mentioned in the query I am able to update one table at time with the same connection There is some more requirement or bug in mysql for multi-table update Regards Ajit - This mail sent through http://shreya.co.in/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: Multi-Table Updates
ajitdixit, Tuesday, November 26, 2002, 4:18:42 PM, you wrote: aasdcdi I have update priviledge on all the tables mentioned in the query aasdcdi I am able to update one table at time with the same connection aasdcdi There is some more requirement or bug in mysql for multi-table update Show me the output of SHOW GRANTS FOR 'your_user'@'user_host' And send me a repeatable test case. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: Multi-Table Updates
Hi , Result for Show Grants is as under SQL result Host: localhost SQL-query: SHOW GRANTS FOR aldixit@localhost; Grants for aldixit@localhost GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'aldixit'@'localhost' IDENTIFIED BY PASSWORD 'xx' GRANT SELECT ON `mysql`.* TO 'aldixit'@'localhost' WITH GRANT OPTION GRANT ALL PRIVILEGES ON `samir`.* TO 'aldixit'@'localhost' WITH GRANT OPTION Table Strecture areas in samir database Database samir - table areas running on Shreya Intranet areas Field Type Null Default Links to Comments acd varchar(5) No 0 aname varchar(30) No depot char(3) No 0 repseq int(2) No 0 abbr char(3) No state char(2) No noreps float(5,2) No 0.00 Indexes : Keyname Type Cardinality Field PRIMARY PRIMARY 365 acd depot INDEX None depot repseq INDEX None repseq Space usage : Type Usage Data 11,544 Bytes Index 15,360 Bytes Overhead 448 Bytes Effective 26,456 Bytes Total 26,904 Bytes Row Statistic : Statements Value Format dynamic Rows 365 Row length ø 30 Row size ø 74 Bytes SQL For Areas CREATE TABLE areas ( acd varchar(5) NOT NULL default '0', aname varchar(30) NOT NULL default '', depot char(3) NOT NULL default '0', repseq int(2) NOT NULL default '0', abbr char(3) NOT NULL default '', state char(2) NOT NULL default '', noreps float(5,2) NOT NULL default '0.00', PRIMARY KEY (acd), KEY depot (depot), KEY repseq (repseq) ) TYPE=MyISAM; Table strecture for Stockists table in samir database Database samir - table Stockists running on Shreya Intranet Stockists Field Type Null Default Links to Comments cucode varchar(7) No Stockist Code d_code char(3) Nodepots - depotid Depot cu_code varchar(4) No Stockist cu_nm varchar(40) No Stockist Name area varchar(5) Yes NULL areas - acd Area a_nm varchar(30) No Area Name d_name varchar(15) Yes NULLDepot Name stkfullname varchar(204) Yes NULLFull Name repseq int(3) Yes NULLRep . Seq Indexes : Keyname Type Cardinality Field repseq INDEX 4 repseq area INDEX 1349 area cucode INDEX 5396 cucode Space usage : Type Usage Data 708,888 Bytes Index 191,488 Bytes Total 900,376 Bytes Row Statistic : Statements Value Format dynamic Rows 5,396 Row length ø 131 Row size ø 167 Bytes SQL For Stockists CREATE TABLE Stockists ( cucode varchar(7) NOT NULL default '', d_code char(3) NOT NULL default '', cu_code varchar(4) NOT NULL default '', cu_nm varchar(40) NOT NULL default '', area varchar(5) default NULL, a_nm varchar(30) NOT NULL default '', d_name varchar(15) default NULL, stkfullname varchar(204) default NULL, repseq int(3) default NULL, KEY repseq (repseq), KEY area (area), KEY cucode (cucode) ) TYPE=MyISAM; Updating Only areas: Affected rows: 1 SQL-query : [Edit] [Create PHP Code] update areas set aname = 'BALAGHAT' where acd = '78193' Updation Succesful Updating Only Stockist Affected rows: 1 SQL-query : [Edit] [Create PHP Code] update Stockists set a_nm = 'BALAGHAT' WHERE area = '78193' Updation Succesful Multi-Table Update with user aldixit SQL-query : update Stockists, areas set a_nm = aname WHERE area = acd MySQL said: update command denied to user: 'aldixit@localhost' for table 'areas' Multi-Table Update with root user Affected rows: 1 SQL-query : [Edit] [Create PHP Code] update Stockists, areas set a_nm = aname WHERE area = acd Updation Succesful I have extensively tested this with different users but the query results are same Regards Ajit Dixit - This mail sent through http://shreya.co.in/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-Table Updates
Hi All, Does anyone have the syntax for multi-table updates? The Online Mysql manual (for version 4.0.5) only has: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] Where as the other multi-table queries (select, delete) have separate definitions examples for multi-table actions. Thanks in advance, Fraser _ Fraser Stuart Logistics IT 77-85Phone: +61 2 9335 1235 Roberts Rd Mobile: +61 419 233 732 Greenacre NSW [EMAIL PROTECTED] Australia 2190 www.toll.com.au _ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Multi-Table Updates
Fraser, Monday, October 14, 2002, 10:50:35 AM, you wrote: FS Does anyone have the syntax for multi-table updates? FS The Online Mysql manual (for version 4.0.5) only has: FS UPDATE [LOW_PRIORITY] [IGNORE] tbl_name FS SET col_name1=expr1 [, col_name2=expr2, ...] FS [WHERE where_definition] FS [ORDER BY ...] FS [LIMIT #] FS Where as the other multi-table queries (select, delete) have separate FS definitions examples for multi-table actions. In 4.0.X ( X =2) there is only pre-version of multi-table updates. This code is not properly tested yet, that is why multi-table updates are not documented. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Multi-table updates in MySQL
Howdy, Multiple table updates are not available in MySQL. I had the same problem and it was driving me crazy... I suggest you try combining your nice query with some PHP code, did work for me! :) good luck, Maarten Roosen -Original Message- From: Phil Kernick [mailto:[EMAIL PROTECTED]] Sent: woensdag 18 september 2002 7:36 To: [EMAIL PROTECTED] Subject: Multi-table updates in MySQL I'm using MySQL v3.23.52. I want to update a column in one table to be based on a calculated expression from a column in another table. Specifically I want something like this: mysql update a set a.f1=b.f1 where a.f2=b.f2; mysql update a, b set a.f1=b.f1 where a.f2=b.f2; The first says table b is unknown, the second doesn't let there be multiple tables in an update. While I could do this programatically using PHP or Perl, it feels like this is something sensible to do in a single line. If there is a way to do this, please let me know directly as I'm not subscribed to the list. Thanks, Phil. -- _-_|\ Phil Kernick E-Mail: [EMAIL PROTECTED] / \ ROTFL Enterprises Mobile: 041 61 ROTFL \_.-*_/ v Humourist, satirist, and probably a few more 'ists to boot! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-table updates in MySQL
I'm using MySQL v3.23.52. I want to update a column in one table to be based on a calculated expression from a column in another table. Specifically I want something like this: mysql update a set a.f1=b.f1 where a.f2=b.f2; mysql update a, b set a.f1=b.f1 where a.f2=b.f2; The first says table b is unknown, the second doesn't let there be multiple tables in an update. While I could do this programatically using PHP or Perl, it feels like this is something sensible to do in a single line. If there is a way to do this, please let me know directly as I'm not subscribed to the list. Thanks, Phil. -- _-_|\ Phil Kernick E-Mail: [EMAIL PROTECTED] / \ ROTFL Enterprises Mobile: 041 61 ROTFL \_.-*_/ v Humourist, satirist, and probably a few more 'ists to boot! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-table updates
Hello: I am testing a new product with MySQL, and so far have been very pleased, especially with the performance. One enhancement that I was looking forward to in 4.0 is described below: Updates that run over multiple tables is harder to do in MySQL. This will, however, be fixed in MySQL 4.0 with multi-table UPDATE and in MySQL 4.1 with subselects. In MySQL 4.0 one can use multi-table deletes to delete from many tables at the same time. See section 6.4.6 DELETE Syntax However, it appears the syntax for multi-table update has not been included in the version I just downloaded, 4.0.0a. Please advise as to when this will be available. I have several UPDATE queries that depend on multiple tables. Since you also do not yet support subselects, with the multi-table update I know of no way to port these queries to MySQL. Let me know if you have a suggestion to do this. Thanks for a great product, I look forward to using it exclusively in our new package. Mike Frederick WayCool Software, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multi-table updates
Mike Frederick writes: Hello: I am testing a new product with MySQL, and so far have been very pleased, especially with the performance. One enhancement that I was looking forward to in 4.0 is described below: Updates that run over multiple tables is harder to do in MySQL. This will, however, be fixed in MySQL 4.0 with multi-table UPDATE and in MySQL 4.1 with subselects. In MySQL 4.0 one can use multi-table deletes to delete from many tables at the same time. See section 6.4.6 DELETE Syntax However, it appears the syntax for multi-table update has not been included in the version I just downloaded, 4.0.0a. Please advise as to when this will be available. I have several UPDATE queries that depend on multiple tables. Since you also do not yet support subselects, with the multi-table update I know of no way to port these queries to MySQL. Let me know if you have a suggestion to do this. Thanks for a great product, I look forward to using it exclusively in our new package. Mike Frederick WayCool Software, Inc. Hi! Multi-table updates are comming in 4.0.2. Syntax will be described in a manual when the above version is released. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
