CODEWITHSUNDEEP
mysqlsqlrename
London Smith
London Smith

Reputation: 1659

Change all columns of tables containing some characters

In MySQL, how to change all columns names of all tables to remove the string "_euro" from columns names?

I just could find a way to search tables having some columns containing "_euro" in their names:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE "%_euro"
    AND TABLE_SCHEMA='my_database'

For example, for the column named price_total_euro I want to rename it as price_total

Upvotes: 1

Views: 108

Answers (3)

IdrisSan
IdrisSan

Reputation: 1

if your using python with MySQL Connector you could place the column headers into a list and then run it through a for loop?

for name in table_names:
    if name.endswith("_euro"):
        new_name = name.replace("_euro", "")

Upvotes: 0

mshahien
mshahien

Reputation: 63

put your select into a stored procedure and make a cursor and loop on all and do the alter operation

you can find example for a procedure here : https://stackoverflow.com/questions/15786240/mysql-create-stored-procedure-syntax-with-delimiter#:~:text=Getting%20started%20with%20stored%20procedure%20syntax%20in%20MySQL%3A,Why%20didn%27t%20this%20work%3F%20...%20More%20items...%20

Upvotes: 0

Luuk
Luuk

Reputation: 14929

Create a script with the following SQL:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," RENAME COLUMN ",COLUMN_NAME," TO ",REPLACE(COLUMN_NAME,"_euro",""),"; ")
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE "%_euro"

output will be, multiple lines which look like this:

ALTER TABLE test.t1_euro RENAME COLUMN t1_euro TO t1;

Review the script, and execute it on your database (after making a backup....)

Upvotes: 2

Related Questions