how to bulk update mysql data

byran cheung · 03-18-2015, 03:34 AM

I have a database , there are some table in it , in this database , there is a string "error" , I would like to change it to "warning" , except export all data to text , find and replace string , and then import back to database , is there other method may bulk update the string ? thanks

TenTenths · 03-18-2015, 04:13 AM

Code:

UPDATE tablename SET thefield = REPLACE(thefield, 'error', 'warning');

From the first hit on google for "update replace MySQL" https://www.google.com/search?q=update+replace+mysql Google is an amazing resource, should try it sometime.

byran cheung · 03-18-2015, 04:30 AM

Quote:

Originally Posted by TenTenths

Code:

UPDATE tablename SET thefield = REPLACE(thefield, 'error', 'warning');

From the first hit on google for "update replace MySQL" https://www.google.com/search?q=update+replace+mysql Google is an amazing resource, should try it sometime.

thanks reply,

sorry to my fool , what is the mean of "thefield" , I just copy your whole command will be fine ?

thanks

TenTenths · 03-18-2015, 04:47 AM

Quote:

Originally Posted by byran cheung

sorry to my fool , what is the mean of "thefield" ,

Replace it with the name of the field you want to change the value in. If you have more than one table or more than one field you'll have to do each table individually.

Quote:

Originally Posted by byran cheung

I just copy your whole command will be fine ?

No, see above, you'll have to change the table and field name appropriately.

byran cheung · 03-18-2015, 11:23 AM

Quote:

Originally Posted by TenTenths

Replace it with the name of the field you want to change the value in. If you have more than one table or more than one field you'll have to do each table individually.
No, see above, you'll have to change the table and field name appropriately.

thanks reply ,
"you'll have to change the table and field name appropriately." <<= is there any command that can change data in all table / field by one time , as I have so many tables .

Thanks

TenTenths · 03-18-2015, 11:30 AM

Quote:

Originally Posted by byran cheung

is there any command that can change data in all table / field by one time

Not as standard. Also beware of doing a global search/replace in a mysqldump file as you may also change table/column names.