In MySQL, how do I do a bulk update to a LIKE query, removing a character?

I have the following MySQL query that pulls back 400 records:

SELECT email FROM user WHERE email LIKE ''

Returns: through

What I want to do is remove the K from all 400 of these e-mail addresses in one easy UPDATE, so the return would be through

How can I do this from a LIKE query? Using RegEx somehow?

>Solution :

Your initial query seems… off:

SELECT email FROM users WHERE user LKE ...

Shouldn’t that be:

SELECT email FROM users WHERE email LIKE ...

Also, LIKE '' should probably be LIKE '', to capture values that actually have a K in it.

I would expect something simple, like using MySQL’s REPLACE() function should handle this:

  email = REPLACE(email, 'GMSDK', 'GMSD')
  email LIKE ''

Edit: In my experience, table names are plural, so users instead of user. If you are indeed using user, adjust as required.

Leave a Reply