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

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!