visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: question regarding update syntax in 3.23 vs. 4.0.1 (multi-table update)
By: daschund U.S.A.  Date: 04/09/2003 00:00:00  English  Points: 75 Status: Answered
Quality : Excellent
Hi all,

I'm performing some data cleaning on a MySQL database (version 3.23.x) -- I have a copy of the database on a local server (MySQL version 4.0.11) to speed things along.

The following SQL statement runs perfectly on the local database:

update orders as a, tmp_order_update as b set a.subtotal=b.subtotal, a.discount=b.discount where a.orderid=b.orderid;

However, it bombs on the 3.23 version of the database. YES, I know, multi-table update syntax was added to MySQL in version 4.x.

My questions are: Can I accomplish the same update in MySQL version 3.23.x using a single SQL statement? And if so, what is the correct update syntax to do so?

Thanks in advance for any/all replies!

Regards,

J
By: VGR Date: 04/09/2003 07:06:00 English  Type : Answer
the answer is "no, not in one single query"

the real answer is to use a front-end script looping throughtmp_orders_update and performing the update line by line on orders
By: daschund Date: 04/09/2003 08:48:00 English  Type : Comment
VGR,

I was afraid of that! (I've got a whole bunch of these scripts lying around from previous, similar updates!!! ;-) )

Thanks,

J
By: Squibi Date: 04/09/2003 09:47:00 English  Type : Assist
Well, assuming you just want to duplicate the cleanup on the 3.x server, why not dump the tables to the 4.x machine, clean them up, and push them back to the 3.x machine using mysqldump in both cases? Of course, the best solution is to update the 3.x box.
By: VGR Date: 05/09/2003 02:20:00 English  Type : Comment
good idea Squibi (ça rime)
By: daschund Date: 08/09/2003 00:41:00 English  Type : Comment
Squibi -

Thanks for the suggestion, but no can do -- the 3.x database is a 'live' e-commerce site. -- I can't dump , fix, and reload using MySQL without running the risk of losing customer records and/or orders added since the dump. I need to do the fix on the 'live' data and that's why I was angling for a solution that I can run on the 3.x database.

I looks like I'll be re-using one of my PHP scripts to do as VGR suggests (and, as I thought I might wind up having to do!)

J

P.S. I'm seeing if our hosting company is able to update our MySQL to the latest stable 4.x release. But, since we're running in a shared environment, it's doubtful -- otherwise, I would have upgraded us to 4.x long ago!

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 278.259040 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page