What is the best way to update a field for each row in a table?

Question

I have a table called artists. Within it, there is a field for the artist name (artist_name). Then there is a field for SEO friendly artist name, we'll call it search_name.

I have over 40,000 artists in this table. So, I'd like to convert all artists names to search friendly. What is the best way to accomplish this? Not looking for code here, just ideas.

This is what I have thus far. I'm just not sure if I should call all 40,000 artists, loop through them and update?

// Does this artist name have any symbols, apostrophes, etc. If so, strip them out

// Does this artist have a space (the beatles)? If so, replace with + (the+beatles).

// insert into search field

Answer 1

You could go through and create a secondary table two columns wide (id, safe) and insert it from there.

Query Table 1

Convert artist names to safe names

Insert into Table 2

Use id of both tables to match them. This would only allow one to one matches though if id is the index, you may want to create a third column if you want multiple safe names for a single artist (id | artistID | artistName)

Answer 2

Please consider using some full-text search engine. For example, the free sphinx search - it's quite flexible, extremely fast and it does support word stemming.

Answer 3

As 40,000 records aren't that much, I'd grab all of them and loop through them in memory. By doing it in memory, unique checks should be pretty fast.

In the end, I'd just chain the commands together like: $query .= "UPDATE artists SET search_name = $generated_name[$i] WHERE id = $id[$i];".

By the way: I'd replace spaces with a minus.