Last night I was working on a project for a customer where the application was going to reference external images after the changes I made. One of the custom parts of the project included modifying the thousands of rows that currently existed in the MySQL database. I needed away to modify one of the columns in a MySQL database table by adding data to the front of that column in each row.
Modify Every Row In A MySQL Table By Adding Data To The Front Of Each Column:
Say the column in the products table was called images the data when I started followed the format of “something.gif” I needed to modify something.gif to domain.com/path/something.gif so even though the images were local they needed to be referenced by the URL after the code changes were made. So all in all I really just needed to add data to the front of every row of one of the columns in a MySQL table. Below is syntax using CONCAT to put together the current data with more data.
Use CONCAT To Add Data To A Specific Row In A MySQL Database Table:
- UPDATE products SET image = CONCAT('somedomain.com/images/', image) WHERE id = '123';
As seen in the above SQL statement the UPDATE syntax actually merges together “somedomain.com/images/” with the data that is already currently in that column for the products id 123. If you want to update every image column in every row in the products table use the below syntax which doesn’t include the WHERE clause.
Use CONCAT To Add Data To Every Row In A MySQL Database Table:
- UPDATE products SET image = CONCAT('somedomain.com/images/', image);
You can also add data to the end by putting the field name first, a comma, and then the data you want to add to the end in quotes.
Learning SQL: Master SQL Fundamentals (Paperback)
|
||||||||
MySQL (4th Edition) (Paperback)
|
||||||||
