How to update multiple rows at the same time with one query

In cleaning a set of data, I came across several entries where there are multiples of a street name such as:

Canal St & Monroe St  

and

Canal St & Monroe St (*)

In total I have 25 street names that are misrecorded as the street name followed by (). I want to update all of the street names so that they are rewritten to drop the () and all read under the original street name. I can update like this:

UPDATE `Bikeshare_data.2019_bike_data`
SET from_station_name = 'Archer (Damen) Ave & 37th St'
WHERE from_station_name ='Archer (Damen) Ave & 37th St (*)'

and it works successfully, but I’d have to run 25 individual queries to do so. I’ll also have to do the same for the end station name. Is there a way I can combine this into one query to update them all at once?

Answer

You can do:

update `Bikeshare_data.2019_bike_data`
    set from_station_name = replace(from_station_name, ' (*)', ''),
        to_station_name = replace(to_station_name, ' (*)', ''),
    where from_station_name like '% (*)' or
          to_station_name like '% (*)';

Note: This assumes that ' (*)' does not appear anywhere else in the names. That seems like a reasonable assumption.