CODEWITHSUNDEEP
mysqlsql
Michael C.
Michael C.

Reputation: 2520

Update multiple tables simultaneously

So I have a number of tables in an SQL, the contents have a key called "hashtag_id" which contains a number (in this case 1-5). I want to quickly go in and change all the items with the id "1" to "5".

Is there a way to do this automatically?

Upvotes: 0

Views: 683

Answers (3)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

update table set hashtag_id = 5
where hashtag_id = 1;

if you have multiple tables, you can do something like that in phpmyadmin (or any mysql management tool)

this will query the information_schema.columns table (describing all your table and columns), and write an update query for every column named hashtag_id)

SELECT CONCAT('UPDATE ', 
               table_name, 
               ' SET ',
               column_name,
               ' = 5 WHERE ',
               column_name,
               ' = 1;' 
             )
FROM information_schema.columns
WHERE column_name = 'hashtag_id';
--maybe add  AND table_schema = <your_schema>

copy and execute the result

This could probably also be done in a stored procedure using dynamic sql (not used to in MySQL, so no example).

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838216

For each table you need to run the following query:

UPDATE yourtable
SET hashtag_id = 5
WHERE hashtag_id = 1

This finds all the rows where hashtag_id is 1 and sets it to 5.

To get the names of all the tables you can run the following query:

SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'your_schema'
AND column_name = 'hashtag_id'

Upvotes: 1

Clark
Clark

Reputation: 900

UPDATE MyTable
SET hashtag_id=5
WHERE hashtag_id=1;

Upvotes: 0

Related Questions