Alter table rename column name

Off-Topic
1

I want to rename the column quote_comments to cfg_commants

ALTER TABLE supp_quote_detail RENAME user_quote_support_comments to user_cfg_support_comments;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to

and i try to change also not working
ALTER TABLE quote_detail CHANGE user_support_comments user_cfg_support_comments text CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

can please guide the rename column …

2

Hello!

You should use “MODIFY” with ALTER TABLE. We have an example of this in the documentation:

Hope that helps!

3

Thanks Galley
but i have data in table and i need to rename the name not the data type of the column.

if use the modify command also i am getting an error message.

ALTER TABLE quote_detail modify user_cfg_comments text;

i.e my table name is quote_detail
in this i have
quote_id int,
user_quote_comments text

now i need to change to user_quote_comments to user_cfg_comments

4

My apologies, I misunderstood your question.

Have you tried:

ALTER TABLE quote_detail CHANGE user_quote_comments user_cfg_comments;

1 Like
5

Thanks, but it is very hard that small command also not able found … thanks for the community you people are a very quick response to help devlopers

6

Hi @sumanamara2020

I frequently rename table in SingleStore. here is a technique I use.

Modifying Table

I would advise to use the table swap approach when you want to modify a table.
There are many advantages for this:

  • Allow you to create the final table , load some data in it an test it.
  • Allow you to keep backup of previous table
  • Swapping table is fairly fast.
  • If you are using a pipeline against a table you want to modify, you’ll have to stop the pipeline and drop it.

Command to swap:

alter table table_a rename to table_a_old ;
alter table table_a_new rename to table_a ;

** Inserting data from previous table **
I use information_schema.columns to generate the list of columns (attributes)

Select group_concat(column_name) from information_schema.columns
Where table_name = ‘’ and table_schema =’’
Order by ordinal_position ;

This will give you the list of columns, commas separated. All you have to do is copy and paste.

This will save you time when writing :

Insert into table_new (column list) select column_list from table_a

  1. Create the new table with proper columns
  2. Insert data from current table using method pointed above
  3. Swap when ready.
3 Likes