CODEWITHSUNDEEP
mysqlphpmyadmin
user3220812
user3220812

Reputation: 197

Updating all rows within a single column using phpMyAdmin

Please could someone help me write an SQL statement which I can use to update a table in my MySQL database using PHPMyAdmin?

Background

I have a database called XYZ and a table called v2rns_content

The table has a number of columns including one called images

Each row in the images column has the same content, apart from the file path which is different Ex: 

{
   "image_intro":"images\/news_images\/Logos\/Logo.png",
   "float_intro":"",
   "image_intro_alt":"",
   "image_intro_caption":"",
   "image_fulltext":"",
   "float_fulltext":"",
   "image_fulltext_alt":"",
   "image_fulltext_caption":""
}

Where images/news_images/Logos/Logo.png is the file path.

I would like to update this file path by adding an additional level e.g.

images/news_images/legacy/Logos/Logo.png

Question:
Is it therefore possible to search my images column for the text images/news_images and replace this with images/news_images/legacy for all the rows within this column?

Notes:
It's worth noting that the v2rns_content table is proving difficult to export(due to size and special characters) otherwise I'd simply find and replace in excel then import it again using phpMyAdmin.

The v2rns_content table contains upwards of 20,000 records

I am not an expert in SQL so would appreciate it (if possible) if I'm able to achieve this using the SQL function in phpMyAdmin.

Thanks for your help in advance. It's much appreciated.

Upvotes: 1

Views: 2953

Answers (1)

Tatarin
Tatarin

Reputation: 1298

Without a good perspective on your schema. In order for you to update all of the columns with the value (same or different, depending what you do with it in PHP).

This will replace the whole value:

UPDATE v2rns_content SET image_intro ='images\/news_images\/legacy' 
WHERE image_intro LIKE '%images\/news_images%'

If you want to replace part of the dir path you might want to do this:

UPDATE v2rns_content 
SET image_intro = REPLACE(image_intro , 'images\/news_images', 'images\/news_images\/legacy')

Here's an official ref page: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace

Upvotes: 1

Related Questions