Replace text in multiple tables and fields in MySQL

Question

I'm attempting to replace particular text strings in multiple tables and fields. I have code to do it for one field in one table, but I don't want to duplicate my efforts (since I have many strings I'd like to replace).

Here's my code for one field in one table:

UPDATE `phpbb3`.`phpbb_posts`
SET `post_subject` = REPLACE(`post_subject`,'string-to-be-replaced','replacement-string')
WHERE ( `post_subject` LIKE '%string-to-be-replaced%' )
ORDER BY `phpbb_posts`.`post_time` DESC

How do I write a command to replace text in both the phpbb_posts.post_subject field and the phpbb_topics.topic_title field?

(Do I need the WHERE or ORDER, since phpMyAdmin doesn't show the results of an UPDATE anyway?)

You don't need the ORDER BY clause, but I'm not sure why you wouldn't need to use a WHERE clause because of phpMyAdmin. UPDATE queries without a WHERE clause affect all rows in the table, so they're generally a bad idea. In this specific instance it may not matter much, because MySQL will be using the replace function to look for string-to-be-replaced and only replace this in any matching rows. But generally, a WHERE clause is the most important thing of an UPDATE query. You don't want all of your users' passwords to be changed because one user changed his password. — Daan
– Daan, Commented May 20, 2012 at 21:36

SuperDJ · Accepted Answer · 2018-07-31 11:18:30Z

2

You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATE statements are treated together. You can also batch them to avoid a round trip.

BEGIN TRANSACTION
UPDATE phpbb_posts
SET post_subject = REPLACE(`post_subject`,'string-to-be-replaced','replacement-string')
WHERE ( `post_subject` LIKE '%string-to-be-replaced%' )
ORDER BY `phpbb_posts`.`post_time` DESC

UPDATE phpbb_topics
SET .topic_title = <something>
WHERE <column> = <something>

COMMIT

edited Jul 31, 2018 at 11:18

SuperDJ

7,69911 gold badges44 silver badges80 bronze badges

answered May 20, 2012 at 21:30

JHS

7,8812 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daan Over a year ago

You probably meant to write "can't" :)

JHS Over a year ago

@Daan - Corrected it. Thanks for spotting it and reporting it.

Collectives™ on Stack Overflow

Replace text in multiple tables and fields in MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related