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]
