SQL query to search and change prefixes

[Morrus]

I need to add large numbers of thread prefixes based on keywords in the thread titles. At the moment, I'm spending hours laboriously searching for keywords, opening threads, editing them, and saving them again- all one at a time. There are inline prefix change mods around, but they don't work on the search results pages.

Could anyone advise on the SQL query I'd need to run to do the following:

Change thread prefix to X for all threads in Forum Y whose title contain the string Z.

(e.g. change thread prefix to Apples for all threads in Food Forum whose titles contain the word "apple").

Thanks in advance for any help!

[kh99]

You need to use the prefix ids and not the label (you can see the ids in the prefix manager). But this should work:

UPDATE thread SET prefixid = 'x' WHERE forumid = Y AND title LIKE '%Z%'

[Morrus]

Thanks! Are those % symbols wildcards?

[Lynne]

Yes, % is a MySQL wildcard.

It is strongly suggested to test queries on a test database before doing so on the live database. Also make sure you do a database backup prior to running any queries on your live database.

[Morrus]

Yep, understood. Thanks!

As a further question - if I want a space in the search string (say "green apples") do I need to use a special character or just use the space as normal?

[kh99]

Spaces are ok because the string is in quotes. But some characters would need to be escaped. If you want to match a literal '%' or '_' (underscore matches any single char), or a single quote, you would need to put a backslash before them.

[Morrus]

Fantastic. Thanks for the help!