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 'GMSD%@law.com'

Returns:

GMSDK001@law.com through GMSDK400@law.com

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 GMSD001@law.com through GMSD400@law.com.

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 'GMSD%@law.com' should probably be LIKE 'GMSDK%@law.com', to capture values that actually have a K in it.

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

UPDATE
  users
SET
  email = REPLACE(email, 'GMSDK', 'GMSD')
WHERE
  email LIKE 'GMSDK%@law.com'

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