How to alter images/ to assets/images in MySQL data?

Question

I have the following table with data.

CREATE TABLE IF NOT EXISTS `omc_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  ...
  ...
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=121 ;

--
-- Dumping data for table `omc_product`
--

INSERT INTO `omc_product` (`id`, `name`, `shortdesc`, `longdesc`, `thumbnail`, `image`, `class`, `grouping`, `status`, `category_id`, `featured`, `other_feature`, `price`) VALUES
(1, 'Doggie', 'Cappelen forlag: New Flight', 'Doggie from New flight.', 'images/newflight_doggie_small.jpg', 'images/newflight_doggie_big.jpg', 'new-flight', 'imagebox-new', 'active', 5, '', 'none', 0.00),
(2, 'Jinnie', 'New flight Jinnie', '', 'images/newflight_jinnie_small.jpg', 'images/newflight_jinnie_big1.jpg', 'new-flight', 'imagebox-new', 'active', 5, '', 'none', 0.00),
(3, 'Trehus', 'Det hemmelige eventyret: tre hus', '', 'images/trehus_small.jpg', 'images/trehus.jpg', 'det-hemmelige-eventyret', 'imagebox-even', 'active', 5, '', 'none', 0.00),
(4, 'Anima Halloween', 'Forlaget fag og kultur...

All the fields of thumbnail and image has images/ at the beginning.

Now I need to add assets/ in front of all this images/.

For example, instead of

'images/newflight_doggie_small.jpg', 'images/newflight_doggie_big.jpg'

I need to change to

'assets/images/newflight_doggie_small.jpg', 'assets/images/newflight_doggie_big.jpg'

Could anyone tell me how to do it please?

Thanks in advance.

Answer 1

Try this:

UPDATE omc_product
SET image = CONCAT('assets/', image), thumbnail = CONCAT('assets/', thumbnail)

MySQL UPDATE syntax, MySQL string functions

If you have to do it more than once, the following solutions are more 'secure':

As Bobby mentions in his comment, you can (should) add a WHERE clause to ensure to not alter the paths that already start with assets:

UPDATE omc_product
SET image = CONCAT('assets/', image), thumbnail = CONCAT('assets/', thumbnail)
WHERE thumbnail NOT LIKE 'assets%' AND image NOT LIKE 'asset%'

But in this way you cannot add assets to either image or thumbnail in case it is only missing in one field.

Another solution could be:

UPDATE omc_product
SET image = IF(image NOT LIKE 'asset%',CONCAT('assets/', image), image), 
thumbnail = IF(thumbnail NOT LIKE 'assets%', CONCAT('assets/', thumbnail), thumbnail)

But note that this updates every row in your DB even if it doesn't need to get updated.