Solved

MySQL update statement help

Posted on 2013-05-20
3
436 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 109

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Instering to MySQL table 5 48
Have issues with Query MySQL 9 64
insert row field data graphically 4 23
Creating Functions in phpMyAdmin 8 13
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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