How to mass edit data inside mysql database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
prasitc2005
Forum Commoner
Posts: 42
Joined: Thu Jul 13, 2006 7:14 am

How to mass edit data inside mysql database

Post by prasitc2005 »

I have data inside a table which needed to be edited like this:

from
name : image : pdf
shirt shirt.gif shirt.pdf
shorts shorts.gif shorts.pdf

into
shirt image/shirt.gif pdf/shirt.pdf
shorts image/shorts.gif pdf/shorts.pdf

Is there a script to mass edit data inside table? Thanks.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

UPDATE `tablename` SET `field` = 'NEW DATA' WHERE `field` = 'OLD DATA';
Last edited by RobertGonzalez on Tue Aug 15, 2006 1:38 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

name, image and pdf are separate columns? Why can't these paths be added during page creation? Redundancy isn't helpful, most often, in databases.
User avatar
prasitc2005
Forum Commoner
Posts: 42
Joined: Thu Jul 13, 2006 7:14 am

Post by prasitc2005 »

Thanks a lot Everah.

One question though, is it possible that some part of the old data remains when we update.

Eg. I want to change the field from

short.gif to image/short.gif and the next fields has different name shirt.gif to image/shirt.gif and on and on

To simplify that, I just want to add the words image/ into the front of every image name in the column. That's why I call it mass editing.

I can't do one by one for sure, especially if I have to add just image/ into 3,000 image names.

How does the sql command work then based on your sql?

Code: Select all

UPDATE `tablename` SET `field` = 'NEW DATA' WHERE `field` = 'OLD DATA';

Code: Select all

update products set image = image/*.gif where image=*.gif


something like this?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

update products set image = concat('image/', image)
User avatar
prasitc2005
Forum Commoner
Posts: 42
Joined: Thu Jul 13, 2006 7:14 am

Post by prasitc2005 »

This is brilliant! The power of CONCAT !!!

More question though. I updated that but still have a slight problem because some of the image names already had image/short.gif so by doing that some names became image/image/short.gif

How can I reduce it just to image/short.gif.

Thanks a lot!!!
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

prasitc2005 wrote:This is brilliant! The power of CONCAT !!!

More question though. I updated that but still have a slight problem because some of the image names already had image/short.gif so by doing that some names became image/image/short.gif

How can I reduce it just to image/short.gif.

Thanks a lot!!!

Code: Select all

UPDATE products SET image = replace(image,'image/image/','image/') WHERE image LIKE 'image/image/%'
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You know, a lot of this information is available in the MySQL manual on Strings.
User avatar
prasitc2005
Forum Commoner
Posts: 42
Joined: Thu Jul 13, 2006 7:14 am

Post by prasitc2005 »

:idea: :D Thank you so much for your help, all cool and kind gurus! You have got all my problems solved. I like PHP MySQL more and more. Hope I see you guys again in my next post. Cheers!
Post Reply