Update field with a shorter string

I need to modify the LENGTH of text in field in db mysql.

I’ve attribute_name that I need to have with max LENGTH 28 characters. WordPress and woocommerce limit this field to 28 characters.

Right now my website is down because some taxonomies are too long. There is a way to “cut” all attribute_name <= 28? All my attribute_name should be max LENGTH 28.

I’ve used:

SELECT * FROM wp_woocommerce_attribute_taxonomies WHERE CHAR_LENGTH(attribute_name) > 28

​x
 
SELECT * FROM wp_woocommerce_attribute_taxonomies WHERE CHAR_LENGTH(attribute_name) > 28 ​

To show only items longer than 28 characters. Result: 669 rows.

Then I’ve tried with:

SELECT attribute_name, SUBSTR(`attribute_name`,1,27) 
FROM wp_woocommerce_attribute_taxonomies 
WHERE char_length(`attribute_name`) > 27

 
SELECT attribute_name, SUBSTR(`attribute_name`,1,27) FROM wp_woocommerce_attribute_taxonomies WHERE char_length(`attribute_name`) > 27​

It show me the correct string, but it doesn’t update my original field.

Someone can help me? Thanks

Answer

Starting from your existing select query, you can just do:

update wp_woocommerce_attribute_taxonomies
set attribute_name = substr(attribute_name, 1, 27)
where char_length(attribute_name) > 27

 
update wp_woocommerce_attribute_taxonomiesset attribute_name = substr(attribute_name, 1, 27)where char_length(attribute_name) > 27 ​

Advertisement

Answer