mySQL table update

DropHit · 02-04-2004, 12:17 AM

I need a SQL statement to update my data this is the lowdown

I have emails in my dbase that are typos

ex:

user@netscap.com - should be 'user@netscape.com'
user@ail.com - should be 'user@aol.com

Now these email addresses are in a table called lm_users and all i need to do is replace the 'ail' part with 'aol' but retain the other part of the address, obviously.

Now i know how to do a replace for each one that is bad, but i need a general SQL statment that will look for all 'ail' addresses in this table and make the appropriate change without affecting the rest of the address.

Sorta like a find-replace in word.

I had thought about a dump file then using just taht a find and replace, just wanted to see if the gurus could save me from having to import and export etc.

Ehhh help

Zack

ter_roshak · 02-04-2004, 12:44 AM

You may want to take a look at the string functions here:

http://www.mysql.com/doc/en/String_functions.html

In particular to this one:

REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str:

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

...and that should work for you.

-Josh

DropHit · 02-04-2004, 12:53 PM

What about this here is theproblem...

I would still need to enter a string right?

ex

I have multiple users with typos for netscap.net should be netscape.net

Would that still work?

Zack

ter_roshak · 02-04-2004, 05:40 PM

You're going to have to perform a SELECT query to obtain those strings... Play around with that, and when I get home tonight I'll whip something up.

-Josh

DropHit · 02-04-2004, 08:55 PM

OK great yeah had some probs with the thing not very good at this i guess

I would VERY MUCH appreciate a whip up!!

Zack

ter_roshak · 02-04-2004, 10:47 PM

DropHit,

Ok, sorry about the delay, I had a tour of an internet exchange tonight, very boring stuff!

All right, just to make sure what I have done works, I have created a test database on my own system here and here is what I have come up with:

Code:

mysql> update <tablename>
       -> set <fieldname> = replace (<fieldname>, 'ail.com', 'aol.com');

...then

mysql> update <tablename>
       -> set <fieldname> = replace (<fieldname>, 'netscap.com', 'netscape.com');

So, what is happening here:

You are updating a table, so you use update <insert name of table here>, then you want to tell it what to update, we will use SET <insert column/field name here> and you want to tell it what to set it to. We will use the replace function, which allows us to enter the fieldname, then the string to get replace within that field, then the string to replace it with. Bear in mind that the user portion of the field will not change, only the portion that matches the middle field of the replace function.

I hope this works for you, if it doesnt, post again and I'll get back to you tomorrow.

-Josh

Code:

Sample output:
Before
mysql> select * from a;
+-------+------------------+
| alpha | beta             |
+-------+------------------+
|     1 | user@netscap.com |
|     2 | user@netscap.com |
|     3 | user@netscap.com |
|     4 | user@netscap.com |
|     5 | user@netscap.com |
|     6 | user@netscap.com |
|     7 | netscap.com      |
|     8 | user@ail.com     |
|     9 | user@ail.com     |
|    10 | user@ail.com     |
|    11 | user@ail.com     |
|    12 | user@ail.com     |
+-------+------------------+
12 rows in set (0.00 sec)

mysql> update a
    -> set beta = replace (beta, 'netscap.com', 'netscape.com');

After
mysql> select * from a;
+-------+-------------------+
| alpha | beta              |
+-------+-------------------+
|     1 | user@netscape.com |
|     2 | user@netscape.com |
|     3 | user@netscape.com |
|     4 | user@netscape.com |
|     5 | user@netscape.com |
|     6 | user@netscape.com |
|     7 | netscape.com      |
|     8 | user@ail.com      |
|     9 | user@ail.com      |
|    10 | user@ail.com      |
|    11 | user@ail.com      |
|    12 | user@ail.com      |
+-------+-------------------+
12 rows in set (0.00 sec)


Also, (just to show you the username wont change, note the replacement here does not make as much sense):

Before
mysql> select * from a;
+-------+-------------------+
| alpha | beta              |
+-------+-------------------+
|     1 | me@netscap.com    |
|     2 | me@netscap.com    |
|     3 | me@netscap.com    |
|     4 | user@netscape.com |
|     5 | user@netscape.com |
|     6 | user@netscape.com |
|     7 | netscape.com      |
|     8 | user@aol.com      |
|     9 | user@aol.com      |
|    10 | user@aol.com      |
|    11 | user@aol.com      |
|    12 | user@aol.com      |
+-------+-------------------+
12 rows in set (0.00 sec)

mysql> update a
    -> set beta = replace (beta, 'netscap', 'netscape.com');
Query OK, 7 rows affected (0.00 sec)
Rows matched: 12  Changed: 7  Warnings: 0

After
mysql> select * from a;
+-------+------------------------+
| alpha | beta                   |
+-------+------------------------+
|     1 | me@netscape.com.com    |
|     2 | me@netscape.com.com    |
|     3 | me@netscape.com.com    |
|     4 | user@netscape.come.com |
|     5 | user@netscape.come.com |
|     6 | user@netscape.come.com |
|     7 | netscape.come.com      |
|     8 | user@aol.com           |
|     9 | user@aol.com           |
|    10 | user@aol.com           |
|    11 | user@aol.com           |
|    12 | user@aol.com           |
+-------+------------------------+
12 rows in set (0.01 sec)