RE: Does Update allow for aliases?
I concede to the MySQL engineer :-) -Original Message- From: Shawn Green [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 3:30 PM To: Jonathan Langevin Cc: Chris White; Richard Reina; mysql@lists.mysql.com Subject: Re: Does Update allow for aliases? Hi all, Multi-table updates are not possible for versions older than 4.0.4. (http://dev.mysql.com/doc/refman/4.1/en/update.html) so the operation is not possible with your current version. To be complete, though, each of you missed the second syntax error in his statement Jonathan Langevin wrote: The proper syntax would need to be: UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; snip The second table is aliased to 'ao' not 'a': UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=ao.ID; ^^ Look here :) An alternative form is: UPDATE maindb.orders o INNER JOIN altdb.orders ao ON o.ID=ao.ID SET o.price=ao.price; The table reference portion of the mulitple-table UPDATE command will accept any valid JOIN syntax, not just the implied INNER JOIN of a comma separated table list. Yours, -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Update allow for aliases?
Richard Reina wrote: I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; First off, it'd be best if possible (I know some cases prevent it) to upgrade your server. The latest stable is 5 and you're on 3, so a lot of people aren't going to be able to vouch that much for any sort of issues regarding it. Also, UPDATE FROM seems to be a non standard SQL extension, and I haven't been able to find anything on MySQL supporting it (Only MsSQL). Feel free to prove me wrong though (in fact I'd love to be proven wrong so I know I'm not going completely crazy ;) ). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Update allow for aliases?
Hi Richard, Richard Reina wrote: I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; If update does not support aliases, is there another way to do this query? I am usin V3.23.54. Any help would be greatly appreciated. I think multi-table update or delete operations are impossible in MySQL 3.23. You should really consider upgrading to a more recent version as the mysql 3 lifecycle ended long ago. You will need a script to do that, it can't be done in pure SQL. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does Update allow for aliases?
The proper syntax would need to be: UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; The only problem is the existence of the from. That being said, an UPDATE ... JOIN likely doesn't work under MySQL 3 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 12:10 PM To: Richard Reina Cc: mysql@lists.mysql.com Subject: Re: Does Update allow for aliases? Richard Reina wrote: I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; First off, it'd be best if possible (I know some cases prevent it) to upgrade your server. The latest stable is 5 and you're on 3, so a lot of people aren't going to be able to vouch that much for any sort of issues regarding it. Also, UPDATE FROM seems to be a non standard SQL extension, and I haven't been able to find anything on MySQL supporting it (Only MsSQL). Feel free to prove me wrong though (in fact I'd love to be proven wrong so I know I'm not going completely crazy ;) ). -- 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: Does Update allow for aliases?
Hi all, Multi-table updates are not possible for versions older than 4.0.4. (http://dev.mysql.com/doc/refman/4.1/en/update.html) so the operation is not possible with your current version. To be complete, though, each of you missed the second syntax error in his statement Jonathan Langevin wrote: The proper syntax would need to be: UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; snip The second table is aliased to 'ao' not 'a': UPDATE maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=ao.ID; ^^ Look here :) An alternative form is: UPDATE maindb.orders o INNER JOIN altdb.orders ao ON o.ID=ao.ID SET o.price=ao.price; The table reference portion of the mulitple-table UPDATE command will accept any valid JOIN syntax, not just the implied INNER JOIN of a comma separated table list. Yours, -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does Update allow for aliases?
Hi Reina, Try like: mysql UPDATE maindb o,altdb ao set o.price =ao.price where o.id=ao.id; This will do good. Thanks ViSolve DB Team - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 10, 2007 10:08 PM Subject: Does Update allow for aliases? I am trying to update from one table to another but I get a syntax error when I try: UPDATE from maindb.orders o, altdb.orders ao SET o.price=ao.price WHERE o.ID=a.ID; If update does not support aliases, is there another way to do this query? I am usin V3.23.54. Any help would be greatly appreciated. Thanks, Richard Your beliefs become your thoughts. Your thoughts become your words. Your words become your actions. Your actions become your habits. Your habits become your values. Your values become your destiny. -- Mahatma Gandhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
