Massive mysql rename. . .best way to do it?

[omegapandor]

Edit: Solved - See thread.

UPDATE table
SET title = REPLACE
(title,'part','Episode')

I have a MySQL database. In the database there are titles that are in the pattern [blah] part [int]. I want to change "part" to "Episode." I attempted exporting the database via phpmyadmin, modifying it in OpenOffice Base, and importing it. . .but I got a "incorrect number of fields errors" when it finally worked. I used the CSV format.

Problem: I do not know if I will be able to upload the database from home. The failed attempt occurred at school where I get more than 3-5K upload bandwidth. I tried the same thing at home the day before, and it still hadn't uploaded after 40 minutes. There's a good chance of it just timing out. Sure, I could do the messy "partial upload" feature. . .or not. I guess if that's the only way to do it, I'll have to try it that way.

Problem:
OpenOffice Base appears to be useless. It's slow, it locks up, and it won't even load SQL format files for me. I loads CSV but everything else that it will load is not importable with phpmyadmin. Maybe I could just change the format with save-->as? This database stuff seems awfully tricky, I dunno. . . I used version 2.2 and portable version 3.0. I see similar complaints from other users. Is there anything free but much better?

Remote connection?:
It looks like there are ways to connect to a database and submit massive numbers of queries. . .but is there a find--->replace feature in such an environment? I know nothing about remotely connection to databases. I have heard of MySQL Query Viewer, but that's about it.

Thanks anyone who helps!

 

[nasaboy007]

You should be able to do an UPDATE query directly from PHPMyAdmin itself and just run it on all of them (no exporting required), but if that keeps timing out (or something of the sort), I suggest you make your own SIMPLE SIMPLE script to do it. For example, you could make a PHP script that would query each row and run an UPDATE query to change the value to what you want it for each row. Then, use something like ob_flush() to flush the output (so that it doesn't look like it's hanging and you can tell progress) and make sure you set the timeout limit to something high.

You could try programs such as MySQL Control Center and MySQL Administrator.

 

[omegapandor]

The REPLACE command fixed this for me. It only took like 0.245 seconds to edit nearly 17,000 records, too. MySQL is soo. . .awesome.

I might not have been able to figure this out were it not for extensive help from amenzl.

Repped.

Code Used:

UPDATE table
SET title = REPLACE
(title,'part','Episode')