LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 02-04-2004, 01:17 AM   #1
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Rep: Reputation: 15
mySQL table update


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
 
Old 02-04-2004, 01:44 AM   #2
ter_roshak
Member
 
Registered: May 2001
Location: Everett, WA
Distribution: Gentoo, RedHat
Posts: 102

Rep: Reputation: 15
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
 
Old 02-04-2004, 01:53 PM   #3
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Original Poster
Rep: Reputation: 15
OK i see but what about...

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
 
Old 02-04-2004, 06:40 PM   #4
ter_roshak
Member
 
Registered: May 2001
Location: Everett, WA
Distribution: Gentoo, RedHat
Posts: 102

Rep: Reputation: 15
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
 
Old 02-04-2004, 09:55 PM   #5
DropHit
Member
 
Registered: Oct 2003
Posts: 95

Original Poster
Rep: Reputation: 15
GREAT yeah had some probs

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
 
Old 02-04-2004, 11:47 PM   #6
ter_roshak
Member
 
Registered: May 2001
Location: Everett, WA
Distribution: Gentoo, RedHat
Posts: 102

Rep: Reputation: 15
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)
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Way to update mysql table 200+ times at once macie Programming 7 04-14-2005 12:45 AM
MySQL db table tommytomato Linux - General 10 08-24-2004 06:57 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 01:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 12:30 PM
deleted mysql table in mysql now cant do anything nakkaya Linux - Software 0 03-18-2003 07:03 PM


All times are GMT -5. The time now is 10:54 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration