Replace a value on ALL tables

Question

I would like to replace the email address email@example.com with another_email@example.com on ALL tables in my MySQL database. How can I do this?

Answer 1

Using the MySQL Workbench, you can search for a string from the "Database" -> "Search Table Data" menu option.

Specify LIKE %URL_TO_SEARCH% and on the left side select all the tables you want to search through. You can use "Cntrl + A" to select the whole tree on the left, and then deselect the objects you don't care about.

Answer 2

You can use below stored procedure-

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_email_update`()
BEGIN
DECLARE done INT(1) DEFAULT 0;
DECLARE _table_name VARCHAR(100) DEFAULT '';
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema='test' AND table_type='base table';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN cur1;

REPEAT
FETCH cur1 INTO _table_name;
IF _table_name = '' THEN
    SET done = 1;
END IF;
             IF (done<>1) THEN
                             SET @str1=CONCAT("UPDATE ",_table_name," SET column1='another_email@example.com' WHERE column1='email@example.com'");
                 PREPARE stmt1 FROM @str1;
                 EXECUTE stmt1;
                 DEALLOCATE PREPARE stmt1;

            END IF;
UNTIL done=1
END REPEAT;
     CLOSE cur1;
     SELECT 'done';
END$$

DELIMITER ;

Create above procedure in db in which you want to change this email value for all tables.

and use below statement to change values-

call sp_email_update();

Note: change your update query as per your requirement. Remove where condition if want on all emails.

Answer 3

you can use this :

<?php

/**
 * do find & replace for a specific value of field in all the databse tables dynamicly.
 * @author Masoud
 * @link  https://github.com/massooti/db_find_replace.git
 */

$pdo = new PDO('mysql:host=db_host;dbname=db_name', 'db_user', 'db_password');

//Our SQL statement, which will select a list of tables from the current MySQL database.
$sql = "SHOW TABLES";

//////Prepare our SQL statement,
$t_statement = $pdo->prepare($sql);

////Execute the statement.
$t_statement->execute();

////Fetch the tables from our statement.
$tables = $t_statement->fetchAll(PDO::FETCH_NUM);

foreach ($tables as $table) {

    $sql = "SHOW COLUMNS FROM $table[0]";

    $c_statement = $pdo->prepare($sql);
    $c_statement->execute();

    $raw_column_data = $c_statement->fetchAll(PDO::FETCH_ASSOC);

    foreach ($raw_column_data as $key => $column) {
        $field = $column['Field'];
        $command = "UPDATE  `$table[0]` SET  `$field` =
                REPLACE($field, 'old_strings', 'new_strings')
                WHERE  $field LIKE 'old_strings%';";
        $f_statement = $pdo->prepare($command);
        $f_statement->execute();
    }
}
?>

Answer 4

write this query for all tables where email id column is available.

UPDATE table_name
SET column1='another_email@example.com'
WHERE column1='email@example.com'