Solved

MySQL update statement help

Posted on 2013-05-20
3
440 Views
Last Modified: 2013-06-05
Hi,
I have mysql table with following data.
I need a small change ,I need to alter email address as below.

Each email like cruz.ashley62@testmail.com need to change as cruz.ashley62+ mydomain@gmail.com
How can I write an update statement for it?

pluto.server2013-05-20-20-21-30-pluto.server.png
0
Comment
Question by:crazywolf2010
  • 2
3 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39182346
update YourTable
set email = replace(email, 'testmail.com', 'gmail.com')
where email like '%testmail.com'
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 250 total points
ID: 39182351
I saw the need to append mydomain. You would do that like this:

update YourTable
set email = replace(email, '@testmail.com', mydomain + '@gmail.com')
where email like '%testmail.com'
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 39182765
I think I would do it this way...

1. Back up the table
2. ALTER TABLE to add a new column, maybe called newEmail
3. SELECT the id and email columns from all the rows and copy them into the newEmail column.
4. SELECT the id and email columns for the rows you want to change.
5. Reformat the email and UPDATE the newEmail column according to a WHERE match on id.
6. Use phpMyAdmin or similar tool to verify that all the changes look right.
7. ALTER TABLE to remove the email column and rename the newEmail column to email.

This strategy may seem slow and plodding, but it allows you and others to use the table while the maintenance is going on.  And if things get screwed up, you have a stable restart point at Step#3.

best of luck with it, ~Ray
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
configure dependency in POM for new database 3 43
mysql ide 10 47
MySQL Backup Strategy 15 53
How efficient to move databases to Azure? 5 51
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question