How to mass edit data inside mysql database
Moderator: General Moderators
- prasitc2005
- Forum Commoner
- Posts: 42
- Joined: Thu Jul 13, 2006 7:14 am
How to mass edit data inside mysql database
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.
- prasitc2005
- Forum Commoner
- Posts: 42
- Joined: Thu Jul 13, 2006 7:14 am
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?
something like this?
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=*.gifsomething like this?
Code: Select all
update products set image = concat('image/', image)- prasitc2005
- Forum Commoner
- Posts: 42
- Joined: Thu Jul 13, 2006 7:14 am
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/%'- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
You know, a lot of this information is available in the MySQL manual on Strings.
- prasitc2005
- Forum Commoner
- Posts: 42
- Joined: Thu Jul 13, 2006 7:14 am