We are about to purchase a site that uses Amazon affiliate links.

Naturally, they have their own affiliate ID, Amazon affiliate links look like this:


What would be the best way to alter the ending string for these links' URL's in a Wordpress blog?

3 Answers 3


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`)
1 row in set (0.00 sec)


and you have Amazon Links like this one:


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


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;
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 !!!

  • Thanks Rolando, however I want to replace the whole string of affiliateID-##. AffiliateID is actually a string of text that contains the name of the affiliate. For example, a website called hobo might have an affiliate ID of hobo-10 and the string I want to replace it with is memi-20.
    – Darwin229
    Feb 18, 2013 at 0:20

I've had some success with this plugin: Search and Replace. You can enter the full URL (including the old affiliate ID) and replace it with the full URL including your new affiliate ID.


Similar to Jared's answer I've used the Velvet Blues Update URLs plugin to you know, update URLs. :) If all the URLs are in the content that should be sufficient. If you think that some URLs might be lurking in other places, like meta/options then you might want to try the Serialized PHP Search and Replace script. I used it recently to move WP to a new server and it was perfect and perfectly simple. I think either would do you well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.