CODEWITHSUNDEEP
mysql
Ank
Ank

Reputation: 6270

Rename column names in MySQL

I have a bunch of columns in MySQL Table T1 with names like 

A/B
C/D
E/F etc

I want to remove the '/' so that the names are AB, CD, EF (etc)

Is there a way(query) to rename all of them together rather than doing it one by one through alter table

Thanks

Upvotes: 0

Views: 2074

Answers (2)

php
php

Reputation: 4425

alter table table_name
            change A/B AB varchar(20),
            change C/D CD varchar(20), 
            change E/F EF varchar(20)

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

The easy way is to write a query that outputs the SQL, then copy-paste the output back into the command line.

Something like this:

select concat('alter table ', table_name, ' change ', column_name, ' ', replace(column_name, '/', ''), ' ', column_type, ';')
from information_schema.columns
where table_schema = 'mydbname'
and column_name like '%/%';

I tested this SQL and it works

Upvotes: 2

Related Questions