3

I have a table with columns like:

emails, country_code etc

Some of the rows contain emails like:

XXXXX@googlemail.com

I want to change about 10,000 records like this in a way that will affect only the "googlemail.com" part of the value and change all of them to "gmail.com".

UPDATE exmple_table SET emails = REPLACE(emails, '%googlemail.com','%gmail.com');

I tried to find and replace but that making me have to type all 10,000 addresses in the query, any solutions?

チーズパン
  • 2,752
  • 8
  • 42
  • 63
Ben
  • 43
  • 6
  • It is unclear what you mean by 'but that making'. Would you not include a WHERE clause in your update statement to match on only rows with emails like ? – Andrew Mortimer Mar 01 '16 at 11:03
  • this isnt my issue, i am able to filter out the rows that i need, but is it possible to write a querry that will affect only the part of the string that is "googlemail.com"? and replace just that part with "gmail.com" and will leave the first part including the "@" intact. im basically trying to fix all the email address that have "googlemail.com" domains instead of "gmail.com".. – Ben Mar 01 '16 at 11:09

1 Answers1

3

You can use 'like' operator to filter out the records which contain 'googlemail' and then perform the string replace on them, as shown below:

update table
set SET emails = REPLACE(emails, 'googlemail.com','gmail.com')
where emails like '%googlemail.com%'
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • 0 row(s) affected Rows matched: 2652 Changed: 0 Warnings: 0 that's what im getting in response :( – Ben Mar 01 '16 at 10:57
  • `select count(*) from table where emails like '%googlemail.com%'`.. How many records does this query return? – Darshan Mehta Mar 01 '16 at 11:07
  • about 10,000 records – Ben Mar 01 '16 at 11:12
  • You also may need to uppercase the column and matching clause. – Andrew Mortimer Mar 01 '16 at 11:12
  • Also, take the % out of the Replace statement. It is attempting to match a literal on that. – Andrew Mortimer Mar 01 '16 at 11:12
  • tried, its not working. if i dont give the % than it tells me that it finds duplicate values and stops the querry – Ben Mar 01 '16 at 11:20
  • @AndrewMortimer thanks for the inputs, updated the answer :) – Darshan Mehta Mar 01 '16 at 11:20
  • @DarshanMehta No problem :) Ben: Do you have a unique constraint on 'emails' ? – Andrew Mortimer Mar 01 '16 at 11:22
  • @AndrewMortimer its the primary key, thanks for the response guys! – Ben Mar 01 '16 at 11:27
  • If it's the primary key and I would suggest creating a temporary table, insert all the records into it with new values and then rename the tables rather than updating primary keys on the same table. – Darshan Mehta Mar 01 '16 at 11:30
  • @DarshanMehta okay, so i created a new table like the one im using, but i didnt set the 'emails' to PK and it works, the only problem is that im left with lots of duplicates, how can i insert those new fixed values to my exsiting table without duplicating? – Ben Mar 01 '16 at 11:39
  • In this case, we may have corrupt/duplicate data. I would write a stored procedure like this (http://stackoverflow.com/questions/4344218/can-i-do-a-mysql-select-update-and-delete-in-one-query) and update only those records which won't cause duplicates. – Darshan Mehta Mar 01 '16 at 11:49