Trying to partially Update a column...

[Jim R]

I have a column with a lot of "wp_1_" in them.  I want them to just be "wp_".  I've backed up my table.

Here is the code I'm looking at:

 

UPDATE wp_usermeta
SET meta_key LIKE "%wp_%"
WHERE meta_key LIKE "%wp_1_%" 

 

 

Is that remotely close?

 

[Psycho]

No, you can't use a LIKE in your set statement - at least not like that. I believe the regex functionality in MySQL is only used for matching, not modifying. Is "wp_1_" at the beginning of the field value or 'within' the field value. If it is at the beginning there is a simple solution. If it is within, you may need to create a PHP script to process the records individually.

 

Ifi "wp_1_" is at the beginning then this should work:

 

UPDATE wp_usermeta
SET meta_key = CONCAT('wp_', SUBSTRING(meta_key, 5))
WHERE meta_key LIKE 'wp_1_%'

[Psycho]

OK, I was wrong. There is a simple solution no matter if the string is at the beginning or within the value

UPDATE wp_usermeta
SET meta_key = REPLACE(meta_key, 'wp_1_', 'wp_')
WHERE meta_key LIKE '%wp_1_%'

[Jim R]

That worked.  Thanks. 

 

And as usual, figuring something out means more work, and that means I'll likely be back in another topic for more help.  I do appreciate all the help provided here. 

[fenway]

There are some magical UDFs for regex -- but when it's simple, explicit is better.