Properly Use Replace Command

I know this seems very basic and I checked Google before posting but
it appears that many of the manuals / guides online reference
something a bit more extreme than what I am looking for.

I am searching a table called 'identities' and in that table there is
a column called 'email'. Some people have the wrong email address
listed so I would like to replace the invalid entry
'user@mail.iamghost.com' to the correct value 'user@iamghost.com'

mysql> select email from identities where email like ('%mail.iamghost.com');

I ran the above command to show me all the people with the invalid
email address. Now I am just looking for a way to change mutiple
incorrect entries in one command. Is this possible in MySQL?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Properly Use Replace Command

Q2FybG9zLA0KDQpJIHRoaW5rIHRoaXMgZG9lcyB3aGF0IHlvdSB3YW50OiAo dW50ZXN0ZWQgdGhv
dWdoKQ0KDQpVUERBVEUgaWRlbnRpdGllcw0KU0VUIGVtYWlsPXJlcGxhY2Uo ZW1haWwsICdtYWls
LicsICcnKQ0KV0hFUkUgZW1haWwgbGlrZSAnJW1haWwuaWFtZ2hvc3QuY29t Jw0KDQoNClJlZ2Fy
ZHMsDQpOYXRoYW4NCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZy b206IENhcmxvcyBX
aWxsaWFtcyBbbWFpbHRvOmNhcmxvc3dpbGxAZ21haWwuY29tXSANClNlbnQ6 IFdlZG5lc2RheSwg
SnVseSAyOSwgMjAwOSAxOjU4IFBNDQpUbzogbXlzcWxAbGlzdHMubXlzcWwu Y29tDQpTdWJqZWN0
OiBQcm9wZXJseSBVc2UgUmVwbGFjZSBDb21tYW5kDQoNCkkga25vdyB0aGlz IHNlZW1zIHZlcnkg
YmFzaWMgYW5kIEkgY2hlY2tlZCBHb29nbGUgYmVmb3JlIHBvc3RpbmcgYnV0 DQppdCBhcHBlYXJz
IHRoYXQgbWFueSBvZiB0aGUgbWFudWFscyAvIGd1aWRlcyBvbmxpbmUgcmVm ZXJlbmNlDQpzb21l
dGhpbmcgYSBiaXQgbW9yZSBleHRyZW1lIHRoYW4gd2hhdCBJIGFtIGxvb2tp bmcgZm9yLg0KDQpJ
IGFtIHNlYXJjaGluZyBhIHRhYmxlIGNhbGxlZCAnaWRlbnRpdGllcycgYW5k IGluIHRoYXQgdGFi
bGUgdGhlcmUgaXMNCmEgY29sdW1uIGNhbGxlZCAnZW1haWwnLiBTb21lIHBl b3BsZSBoYXZlIHRo
ZSB3cm9uZyBlbWFpbCBhZGRyZXNzDQpsaXN0ZWQgc28gSSB3b3VsZCBsaWtl IHRvIHJlcGxhY2Ug
dGhlIGludmFsaWQgZW50cnkNCid1c2VyQG1haWwuaWFtZ2hvc3QuY29tJyB0 byB0aGUgY29ycmVj
dCB2YWx1ZSAndXNlckBpYW1naG9zdC5jb20nDQoNCm15c3FsPiBzZWxlY3Qg ZW1haWwgZnJvbSBp
ZGVudGl0aWVzIHdoZXJlIGVtYWlsIGxpa2UgKCclbWFpbC5pYW1naG9zdC5j b20nKTsNCg0KSSBy
YW4gdGhlIGFib3ZlIGNvbW1hbmQgdG8gc2hvdyBtZSBhbGwgdGhlIHBlb3Bs ZSB3aXRoIHRoZSBp
bnZhbGlkDQplbWFpbCBhZGRyZXNzLiBOb3cgSSBhbSBqdXN0IGxvb2tpbmcg Zm9yIGEgd2F5IHRv
IGNoYW5nZSBtdXRpcGxlDQppbmNvcnJlY3QgZW50cmllcyBpbiBvbmUgY29t bWFuZC4gSXMgdGhp
cyBwb3NzaWJsZSBpbiBNeVNRTD8NCg0KLS0gDQpNeVNRTCBHZW5lcmFsIE1h aWxpbmcgTGlzdA0K
Rm9yIGxpc3QgYXJjaGl2ZXM6IGh0dHA6Ly9saXN0cy5teXNxbC5jb20vbXlz cWwNClRvIHVuc3Vi
c2NyaWJlOiAgICBodHRwOi8vbGlzdHMubXlzcWwuY29tL215c3FsP3Vuc3Vi PW5zdWxsaXZhbkBj
YXBwZXguY29tDQoNCg==

Re: Properly Use Replace Command

On Wed, Jul 29, 2009 at 3:07 PM, Nathan Sullivan wrote:
> Carlos,
>
> I think this does what you want: (untested though)
>
> UPDATE identities
> SET email=replace(email, 'mail.', '')
> WHERE email like '%mail.iamghost.com'

That worked perfect!

UPDATE identities
-> SET email=replace(email, 'mail.', '')
-> WHERE email like '%mail.iamghost.com';

Query OK, 47 rows affected (0.01 sec)
Rows matched: 47 Changed: 47 Warnings: 0

mysql> select name, email from identities where email like
('%mail.iamghost.com');

Empty set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org