Welcome to WebmasterWorld Guest from 3.80.6.254

Forum Moderators: open

Message Too Old, No Replies

Update 1 Column in Database

     
6:36 pm on Sep 18, 2018 (gmt 0)

Junior Member from US 

5+ Year Member

joined:Dec 1, 2012
posts: 90
votes: 0


Hello - I need to change all JPG extensions to jpg in my database. I usually use an update file that works so made up a new update file with below but none of the image extensions changed to jpg. No errors.

"UPDATE tablename SET imgfield = REPLACE(imgfield, 'JPG', 'jpg') WHERE RIGHT('imgfield', 3) = 'JPG'";

Thx in advance for any help.
6:57 pm on Sept 18, 2018 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5852
votes: 199


Did you try without the single quotes around imgfield?
"UPDATE tablename SET imgfield = REPLACE(imgfield, 'JPG', 'jpg') WHERE RIGHT(imgfield, 3) = 'JPG'";
7:23 pm on Sept 18, 2018 (gmt 0)

Junior Member from US 

5+ Year Member

joined:Dec 1, 2012
posts: 90
votes: 0


Thx so much! This finally working will save me lots of time.
7:44 pm on Sept 18, 2018 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5852
votes: 199


Glad it worked!

BTW, what I often do before running a major UPDATE or DELETE call is do a reality check and run a SELECT to make sure the pool of records will be the right ones. For example,
"SELECT imgfield, REPLACE(imgfield, 'JPG', 'jpg') FROM tablename WHERE RIGHT(imgfield, 3) = 'JPG' LIMIT 10";

If the results are what you'd expect, then go ahead and run the UPDATE (or DELETE).

Much better to scratch your head trying to figure out why everything is being selected for deletion than scratching your head wondering where all your data went. :)
9:11 pm on Sept 18, 2018 (gmt 0)

Junior Member from US 

5+ Year Member

joined:Dec 1, 2012
posts: 90
votes: 0


Ha! Thank you. I'll do that ...