Concatenate table name on UPDATE statement using MySQL user-defined variable

Question

I am trying to set the prefix on table names for an UPDATE statement. I have to run this UPDATE statement dozens of times on multiple databases, each has a different table prefix.

The code below does not work, but this is the idea of what I am trying to accomplish.

SET @prefix = 'prefix_';
SET @old = "old_value";
SET @new = "new_value";
UPDATE CONCAT(@prefix, 'table1') SET some_field = REPLACE(some_field, @old, @new);
UPDATE CONCAT(@prefix, 'table2') SET some_field = REPLACE(some_field, @old, @new);
UPDATE CONCAT(@prefix, 'table3') SET some_field = REPLACE(some_field, @old, @new);

Written manually would be...

UPDATE prefix_table1 SET some_field = REPLACE(some_field, 'old_value', 'new_value');
UPDATE prefix_table2 SET some_field = REPLACE(some_field, 'old_value', 'new_value');
UPDATE prefix_table3 SET some_field = REPLACE(some_field, 'old_value', 'new_value');

John Woo · Accepted Answer · 2013-07-26 19:08:51Z

4

use Dynamic SQL on this,

SET @prefix = 'prefix_';
SET @old = "old_value";
SET @new = "new_value";

SET @sql1 = CONCAT('UPDATE ', @prefix, 'table1 SET some_field = REPLACE(some_field,?,?)');
SET @sql2 = CONCAT('UPDATE ', @prefix, 'table2 SET some_field = REPLACE(some_field,?,?)');
SET @sql3 = CONCAT('UPDATE ', @prefix, 'table3 SET some_field = REPLACE(some_field,?,?)');

PREPARE stmt1 FROM @sql1;
PREPARE stmt2 FROM @sql2;
PREPARE stmt3 FROM @sql3;

EXECUTE stmt1 USING @old, @new;
EXECUTE stmt2 USING @old, @new;
EXECUTE stmt3 USING @old, @new;

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
DEALLOCATE PREPARE stmt3;

Prepared Statements

answered Jul 26, 2013 at 19:08

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concatenate table name on UPDATE statement using MySQL user-defined variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related