Update 1 Column in Database

Forum Moderators: open

Message Too Old, No Replies

Update 1 Column in Database

actolearn

6:36 pm on Sep 18, 2018 (gmt 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.

LifeinAsia

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

Did you try without the single quotes around imgfield?

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

actolearn

7:23 pm on Sep 18, 2018 (gmt 0)

Thx so much! This finally working will save me lots of time.

LifeinAsia

7:44 pm on Sep 18, 2018 (gmt 0)

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. :)

actolearn

9:11 pm on Sep 18, 2018 (gmt 0)

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