Find and Replace a letter/number and batch update with MySQL

I had the need to do a batch update of a large MySQL data table that held thousands of products and their information, including SKU numbers. Recently the company has changed how they manage the SKU numbers and needed to replace the leading ‘2’ with a ‘4’ for simple accounting tracking.

It was easy enough to use REGEX to find which ones started with ‘2’, but I was still unsure of how to replace the ‘2’ with a ‘4’ in all of the necessary records.

I knew that if the SKU started with a ‘2’, that it had to be changed to a ‘4’ so I started out using the IF() function to compare SKU’s as follows.

SELECT sku, IF(TRIM(LEADING '2' FROM sku) <> SUBSTRING(sku FROM 2),sku,INSERT(TRIM(LEADING '2' FROM sku),1,0,'4')
)
FROM product

So what this does is select all the SKU’s in the table and return the ones that need to be changed with what the change would look like. The first TRIM() function takes all of the SKUs and trims off the first 2, then it is compared to a substring of the original SKU starting at the second position. If there is no leading ‘2’ in the SKU, then it will not match the substring since nothing was trimmed.

The next argument is telling the function what to return if the TRIM() and SUBSTRING() values differ. It will return the original SKU since we don’t need to change it if it doesn’t start with ‘2’.

The last argument is telling it what to return if TRIM and SUBSTRING are equal. For this value I’m trimming the leading ‘2’ and INSERTing a ‘4’ at the first position of the SKU.

The above query lists the original SKU and what it should look like once I put it into an UPDATE query.

The UPDATE doesn’t look much different:

UPDATE product
SET sku =
IF(
  TRIM(LEADING '2' FROM sku) <> SUBSTRING(sku FROM 2),sku,INSERT(TRIM(LEADING '2' FROM sku),1,0,'4')
)

This actually updates the table with the changes that I wanted. There may have been a more elegant way to do this with REGEXP but this did the trick. I verified that only the SKUs with a leading 2 were changed to a leading 4 and the others were left as they were.

Be Sociable, Share!

Published by

Chris Malpass

I'm a web developer, photographer, casual gamer, and technophile from Virginia, USA.