Is there a way in Expression Engine to bulk close entries that contain a specific string?

Question

I have old data that has been migrated into a new design. The old data contains multi-language data using 4 different languages and Transcribe.

The new site does not contain transcribe, nor does it need the articles in the other languages. But all other articles in english need to be included.

I need to close all entries that are written in these other languages. Removing Transcribe didn't close the entries. In the url_title column in the exp_channel_titles row, the language prefix is included. So I can search for ru, ar, fa - etc in this row, filter them out, and set the status to closed if the strings ru, ar, fa, etc are found.

So I'd like to search for all entries that contain "ar" and bulk close them. I'd imagine there is a way to do this via sql query.

I was able to write:

UPDATE exp_channel_titles a
    ON a.url_title = '"-ru"'
SET a.status = "closed";

based on the response below, but that gave me a syntax error. I'm not sure that = will find a string, it seems like an exact search, I could be wrong.

Answer 1

Here ya go, try this if the url_title will contain -ru:

UPDATE exp_channel_titles a
SET a.status = "closed"
WHERE a.url_title LIKE '%-ru%';

but use this if it ends in -ru:

UPDATE exp_channel_titles a
SET a.status = "closed"
WHERE a.url_title LIKE '%-ru';

Answer 2

This should do it. It checks channel_entry_data in that field for

dir="rtl"

and matches up those entry_ids to the exp_channel_title table and sets the status to closed. This is untested, but exactly what you want (or a quote misplacement or two away):

UPDATE exp_channel_title a
    JOIN exp_channel_data b 
    ON a.entry_id = b.entry_id AND b.field_id_1 = 'dir="rtl"'
SET a.status = "closed";

Let me know how it goes, and as always, back up those two tables before running this query. Also, you'll probably have to prepend your table names with your database name, so

exp_channel_title

becomes

database_name.exp_channel_title

it depends on if you run the query yourself or in a template {exp:query} tag or via the Database Class ( ee()->db->update(this)->join(that)->etc... ).