CODEWITHSUNDEEP
mysqlwordpressphpmyadmin
Maaz
Maaz

Reputation: 4303

How to Bulk Update post titles wordpress through mySQL

All my post titles have a static word in front of them. I have over 9000 published posts with two different static words in my post titles. I am trying to remove this word from all my posts.

Essentially, I am looking for a way to remove that constant word. I tried using export through wordpress and editing the post titles that way, but the file was 100mb + and simply won't open.

I looked towards SQL/phpmyadmin but I am not very well versed with SQL queries and don't want to mess up my database.

The "Constant" is always the first word of the title. What could I do to instead have mysql detect the first space and remove everything before it as well as including the space. Essentially, removing the first word. Basically I have more than one constant, so it would be better if I could just find the first space in the string and then remove it and everything before it. I'm assuming we'd use sub_string or something.

This is the title structure

Constant other stuff here

so if there is a search query where it finds "Constant " and a space and replaces it with "" nothing, that way I could have it completely removed. It'd be great if it could be a search and replace query, so I could utilize it later.

Information on the Database / Table / Column

the table is called: wp_posts and it needs to be restricted to the value of the table post_type when it's value is post and the title is in the post_title column

Any help would be greatly appreciated.

Upvotes: 0

Views: 4696

Answers (2)

fenway
fenway

Reputation: 446

MySQL doesn't have exactly what you're looking for -- yes, there is a REPLACE() string function, but you can't limit it to a single substitution. As such, you might inadvertently replace other occurrences of this constant that could conceivably appear in your title string.

IMHO, the easiest way is to find all titles starting with your constant, and just replace the first one (i.e. at the start of the string):

UPDATE wp_posts
SET post_title = 
MID( post_title, LENGTH('Constant ')+1 )
WHERE post_title LIKE 'Constant %'
AND post_type = 'post';

You need the +1 because MySQL string offsets start at 1, not zero.

Personally, I always prefer to run the equivalent SELECT first, just to be certain (too many years of MyISAM without BEGIN WORK):

SELECT post_title,
MID( post_title, LENGTH('Constant ')+1 ) AS replacedTitle 
FROM wp_posts
WHERE post_title LIKE 'Constant %'
AND post_type = 'post';

Alternatively, if you're certain that you always want to remove the first word (i.e. up to and including the first space), then the following statement should work:

UPDATE wp_posts
SET post_title = 
MID( post_title, POSITION( ' ' IN post_title )+1 )
WHERE post_type = 'post';

Since POSITION() will return zero if no space is found, this statement will be a no-op (i.e. non-destructive) in the general case.

Upvotes: 1

markratledge
markratledge

Reputation: 17561

Search RegEx is a great plugin to be able to search and replace - with grep or plain text - through all post and page content, post titles, post meta, etc. Does not search custom post types.

Back up your DB before making any changes, either with this plugin or direct query in the database.

Docs: http://urbangiraffe.com/plugins/search-regex/

Upvotes: 1

Related Questions