Jim R Posted December 14, 2011 Share Posted December 14, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/253173-trying-to-partially-update-a-column/ Share on other sites More sharing options...
Psycho Posted December 14, 2011 Share Posted December 14, 2011 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_%' Quote Link to comment https://forums.phpfreaks.com/topic/253173-trying-to-partially-update-a-column/#findComment-1297916 Share on other sites More sharing options...
Psycho Posted December 14, 2011 Share Posted December 14, 2011 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_%' Quote Link to comment https://forums.phpfreaks.com/topic/253173-trying-to-partially-update-a-column/#findComment-1297921 Share on other sites More sharing options...
Jim R Posted December 15, 2011 Author Share Posted December 15, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/253173-trying-to-partially-update-a-column/#findComment-1298001 Share on other sites More sharing options...
fenway Posted December 19, 2011 Share Posted December 19, 2011 There are some magical UDFs for regex -- but when it's simple, explicit is better. Quote Link to comment https://forums.phpfreaks.com/topic/253173-trying-to-partially-update-a-column/#findComment-1299212 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.