Replacing link URL ending strings via MySQL

DISCLAIMER : Not a WordPress Developer, Just a MySQL DBA

For a given table, say link_url

mysql> show create table wp_links\G
*************************** 1. row ***************************
       Table: wp_links
Create Table: CREATE TABLE `wp_links` (
  `link_id` bigint(20) unsigned NOT NULL auto_increment,
  `link_url` varchar(255) NOT NULL default '',
  `link_name` varchar(255) NOT NULL default '',
  `link_image` varchar(255) NOT NULL default '',
  `link_target` varchar(25) NOT NULL default '',
  `link_description` varchar(255) NOT NULL default '',
  `link_visible` varchar(20) NOT NULL default 'Y',
  `link_owner` bigint(20) unsigned NOT NULL default '1',
  `link_rating` int(11) NOT NULL default '0',
  `link_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `link_rel` varchar(255) NOT NULL default '',
  `link_notes` mediumtext NOT NULL,
  `link_rss` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`link_id`),
  KEY `link_visible` (`link_visible`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

and you have Amazon Links like this one:

http://www.amazon.com/gp/product/productcode/?tag=affiliateID-20

and you want to replace the number 20 with some other number (like 253) so that URLs are now

http://www.amazon.com/gp/product/productcode/?tag=affiliateID-253

Run the following SQL script

SET @old_number = 20;
SET @new_number = 253;
SET @affiliate_prefix = 'http://www.amazon.com/gp/product/productcode/?tag=affiliateID-';
SET @old_affiliate = CONCAT(@affiliate_prefix ,@old_number);
SET @new_affiliate = CONCAT(@affiliate_prefix ,@new_number);
CREATE TABLE linkids SELECT link_id FROM wp_links WHERE 1=2;
ALTER TABLE linkids ADD PRIMARY KEY;
INSERT INTO linkids SELECT link_id FROM wp_links WHERE link_url=@old_affiliate;
UPDATE linkids A INNER JOIN wp_links B USING (link_id) SET B.link_url=@new_affiliate;
DROP TABLE linkids;

Give it a Try !!!

Related Posts: