Page 1 of 1

How to mass edit data inside mysql database

Posted: Tue Aug 15, 2006 1:34 pm
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.

Posted: Tue Aug 15, 2006 1:37 pm
by RobertGonzalez

Code: Select all

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

Posted: Tue Aug 15, 2006 1:38 pm
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.

Posted: Tue Aug 15, 2006 5:30 pm
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?

Posted: Tue Aug 15, 2006 6:24 pm
by Weirdan

Code: Select all

update products set image = concat('image/', image)

Posted: Tue Aug 15, 2006 6:45 pm
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!!!

Posted: Tue Aug 15, 2006 7:08 pm
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/%'

Posted: Tue Aug 15, 2006 7:16 pm
by RobertGonzalez
You know, a lot of this information is available in the MySQL manual on Strings.

Posted: Wed Aug 16, 2006 2:43 am
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!