Combine multiple UPDATE SET REPLACE statements-CodePudding

I have a fairly large database with some 400k posts and growing, so I am forced to split the UPDATE SET REPLACE statement based on strings to be replaced and then based post ID ranges in order to avoid time outs. As a result, I have many statements that go like this:

UPDATE wp_posts SET post_content = REPLACE(post_content,'0/','0/') WHERE post_type = 'post' AND ID BETWEEN 1 AND 200000;

UPDATE wp_posts SET post_content = REPLACE(post_content,'0 /','0/') WHERE post_type = 'post' AND ID BETWEEN 200001 AND 400000;

UPDATE wp_posts SET post_content = REPLACE(post_content,'1 /','1/') WHERE post_type = 'post' AND ID BETWEEN 1 AND 200000;

UPDATE wp_posts SET post_content = REPLACE(post_content,'1 /','1/') WHERE post_type = 'post' AND ID BETWEEN 200001 AND 400000;

and they go on with '2 /','2/', then '3 /','3/', up to '9 /','9/'. The ID ranges are in fact much smaller, so the number of UPDATES becomes too big to do manually one-by-one on a daily basis.

I looked at stored procedure but couldn't figure it out.

Is there a way to combine these?

Please suggest. Thank you!

CodePudding user response：

You may use a CASE expression:

UPDATE wp_posts
SET post_content = CASE WHEN ID BETWEEN 1 AND 200000
                        THEN REPLACE(REPLACE(post_content, '0/', '0/'), '1 /', '1/')
                        WHEN ID BETWEEN 200001 AND 400000
                        THEN REPLACE(REPLACE(post_content,'0 /','0/'), '1 /', '1/') END
WHERE post_type = 'post' AND ID BETWEEN 1 AND 400000;

CodePudding user response：

In MariaDB you could make

CREATE TABLE mytable (id int ,posts varchar(20))

INSERT INTO mytable VALUES (

UPDATe mytable SET posts = REGEXP_REPLACE(posts, '([0-9] )([ ])([/])', '\\1\\3') 
WHERE id BETWEEN 0 AND 20000

SELECT * FROM mytable

id | posts
-: | :----
 1 | 0/   
 2 | 3/   
 3 | 9/

db<>fiddle here

But maintenance is normal for everyday system, look when you have the least traffic in the site and then set the maintenance mode and run the query above in the console, and not over a gui.

If you don't have a plan for maintenance breaks, make one, test it om a local system, in future if the database is even bigger with billions of rows, the process will take more and more time also backups will become impossible, so that you will take the time and look for replication