RE: Any explanation for this, please
Thank you Mikhail Berman -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 29, 2006 3:30 PM To: Mikhail Berman; mysql@lists.mysql.com Subject: RE: Any explanation for this, please At 14:41 -0500 11/29/06, Mikhail Berman wrote: >Thank you, Sir. > >So, there is no way to plan update query? > Not except in the sense of getting a plan for a similar SELECT statement. >Regards, > >Mikhail Berman > >-Original Message- >From: Paul DuBois [mailto:[EMAIL PROTECTED] >Sent: Wednesday, November 29, 2006 2:37 PM >To: Mikhail Berman; mysql@lists.mysql.com >Subject: Re: Any explanation for this, please > >At 14:31 -0500 11/29/06, Mikhail Berman wrote: >>Dear List, >> >>Could I get an explanation why 1 fails, but 2 works. > >Because EXPLAIN is used only with SELECT statements. >Try to use a similar SELECT. > > >> >>1. >> >>Update statement preceded with explain, fails: >> >>explain update COMPANY_NUMBERS_tmp_Mikhail c join >>tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and >>t.InterimEndingDate = >>replace(left(c.date_qtr,7),'-','/') set >>c.total_shares_outstanding_market_cap = t.MarketValue; ERROR 1064 >>(42000): You have an error in your SQL syntax; check the manual that >>corresponds to your MySQL server version for the right syntax to use >>near 'update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES >>t on c.ticker =' at line 1 >> >>2. >> >>Actual execution of the update statement goes through, no problem >> >>mysql> update COMPANY_NUMBERS_tmp_Mikhail c join >>mysql> tmp_HEMSCOTT_MKTVALUES >>t on c.ticker = t.TickerSymbol and t.InterimEndingDate = >>replace(left(c.date_qtr,7),'-','/') >>set c.total_shares_outstanding_market_cap = t.MarketValue; Query OK, >>157551 rows affected (4 min 22.81 sec) Rows matched: 162999 Changed: >>157551 Warnings: 0 >> >>Regards, >> >>Mikhail Berman >> > > >-- >Paul DuBois, MySQL Documentation Team >Madison, Wisconsin, USA >MySQL AB, www.mysql.com > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql To unsubscribe: >http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Any explanation for this, please
At 14:41 -0500 11/29/06, Mikhail Berman wrote: Thank you, Sir. So, there is no way to plan update query? Not except in the sense of getting a plan for a similar SELECT statement. Regards, Mikhail Berman -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 29, 2006 2:37 PM To: Mikhail Berman; mysql@lists.mysql.com Subject: Re: Any explanation for this, please At 14:31 -0500 11/29/06, Mikhail Berman wrote: Dear List, Could I get an explanation why 1 fails, but 2 works. Because EXPLAIN is used only with SELECT statements. Try to use a similar SELECT. 1. Update statement preceded with explain, fails: explain update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker =' at line 1 2. Actual execution of the update statement goes through, no problem mysql> update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; Query OK, 157551 rows affected (4 min 22.81 sec) Rows matched: 162999 Changed: 157551 Warnings: 0 Regards, Mikhail Berman -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Any explanation for this, please
Thank you, Sir. So, there is no way to plan update query? Regards, Mikhail Berman -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 29, 2006 2:37 PM To: Mikhail Berman; mysql@lists.mysql.com Subject: Re: Any explanation for this, please At 14:31 -0500 11/29/06, Mikhail Berman wrote: >Dear List, > >Could I get an explanation why 1 fails, but 2 works. Because EXPLAIN is used only with SELECT statements. Try to use a similar SELECT. > >1. > >Update statement preceded with explain, fails: > >explain update COMPANY_NUMBERS_tmp_Mikhail c join >tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and >t.InterimEndingDate = >replace(left(c.date_qtr,7),'-','/') set >c.total_shares_outstanding_market_cap = t.MarketValue; ERROR 1064 >(42000): You have an error in your SQL syntax; check the manual that >corresponds to your MySQL server version for the right syntax to use >near 'update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES >t on c.ticker =' at line 1 > >2. > >Actual execution of the update statement goes through, no problem > >mysql> update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES >t on c.ticker = t.TickerSymbol and t.InterimEndingDate = >replace(left(c.date_qtr,7),'-','/') >set c.total_shares_outstanding_market_cap = t.MarketValue; Query OK, >157551 rows affected (4 min 22.81 sec) Rows matched: 162999 Changed: >157551 Warnings: 0 > >Regards, > >Mikhail Berman > -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.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: Any explanation for this, please
On Wed, 2006-11-29 at 14:31 -0500, Mikhail Berman wrote: > Dear List, > > Could I get an explanation why 1 fails, but 2 works. > > 1. Update statement preceded with explain, fails: > > explain update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES > t on c.ticker = t.TickerSymbol and t.InterimEndingDate = > replace(left(c.date_qtr,7),'-','/') set > c.total_shares_outstanding_market_cap = t.MarketValue; > ERROR 1064 (42000): You have an error in your SQL syntax; check the > manual that corresponds to your MySQL server version for the right > syntax to use near 'update COMPANY_NUMBERS_tmp_Mikhail c join > tmp_HEMSCOTT_MKTVALUES t on c.ticker =' at line 1 > Explain only works with tables and SELECT queries: http://dev.mysql.com/doc/refman/4.1/en/explain.html -- Pat Adams Digital Darkness Promotions Dallas Music Wiki http://digitaldarkness.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any explanation for this, please
At 14:31 -0500 11/29/06, Mikhail Berman wrote: Dear List, Could I get an explanation why 1 fails, but 2 works. Because EXPLAIN is used only with SELECT statements. Try to use a similar SELECT. 1. Update statement preceded with explain, fails: explain update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker =' at line 1 2. Actual execution of the update statement goes through, no problem mysql> update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; Query OK, 157551 rows affected (4 min 22.81 sec) Rows matched: 162999 Changed: 157551 Warnings: 0 Regards, Mikhail Berman -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any explanation for this, please
Dear List, Could I get an explanation why 1 fails, but 2 works. 1. Update statement preceded with explain, fails: explain update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker =' at line 1 2. Actual execution of the update statement goes through, no problem mysql> update COMPANY_NUMBERS_tmp_Mikhail c join tmp_HEMSCOTT_MKTVALUES t on c.ticker = t.TickerSymbol and t.InterimEndingDate = replace(left(c.date_qtr,7),'-','/') set c.total_shares_outstanding_market_cap = t.MarketValue; Query OK, 157551 rows affected (4 min 22.81 sec) Rows matched: 162999 Changed: 157551 Warnings: 0 Regards, Mikhail Berman
