MariaDB Rename Column + Examples

In this MariaDB tutorial, we will study the use of the MariaDB Rename column and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB rename column
• MariaDB rename column name
• MariaDB rename column name in the table
• MariaDB rename column in select
• MariaDB rename column error
• MariaDB alter rename column
• MariaDB rename column if exists
• MariaDB rename primary key column

MariaDB Rename Column

In this section, we will learn how to use the RENAME clause for the column with the help of the ALTER TABLE statement in the MariaDB with given syntax and examples.

In MariaDB, the ALTER TABLE can be used to modify rename of the table and it is also used to add, delete, drop or modify columns of the table.

The below syntax shows an implementation of RENAME clause using the ALTER TABLE statement in MariaDB:

ALTER TABLE <table_name>
RENAME COLUMN <old_col_name> <new_col_name> 
[datatype] constraint
[FIRST | AFTER column_name]

The syntax explanation:

The sample example to RENAME the column of the table with the help of the CHANGE clause by using the ALTER TABLE statement is given below:

• table_name: The table name that we want to modify
• old_col_name: The old column name that we want to rename.
• new_col_name: The old column that we want to change new column name as per user’s requirement.
• datatype constraint: The definition of the column datatype can be (NULL or NOTNULL etc). We must specify the column definition when renaming the column if it doesn’t change.
• FIRST | AFTER column_name: It will inform MariaDB where in the table for position of the column if we change its position.

ALTER TABLE USA_HP 
CHANGE COLUMN HP_NAME NAME_HP
 VARCHAR(125);

In this query, the ALTER TABLE will rename the column called HP_NAME to NAME_HP and the column is defined as a VARCHAR(125) data type that doesn’t allow NULL values. If we want to check ALTER TABLE work then use the SELECT statement for the USA_HP table as given below:

SLEECT * FROM USA_HP;

In this image, the column name has been changed from HP_NAME to NAME_HP by using the ALTER statement and only the purpose of the SELECT statement is used to retrieve all the records with the column name from the USA_HP table.

Read: MariaDB Order By Clause

MariaDB Rename Column Name

In this section, we will learn how to use the MariaDB Rename Column Name with the help of the CHANGE clause as a new NAME column in the MariaDB with given syntax and examples.

The syntax to rename column name as NAME column by using the ALTER TABLE statement is given below:

ALTER TABLE table_name
CHANGE COLUMN old_col_name new_col_name 
column_definition
[ FIRST | AFTER column_name ];

In MariaDB, to change the column name as NAME we can only use the ALTER TABLE statement as shown below:

ALTER TABLE CHANGE COLUMN hp_name NAMES varchar(!20);

SELECT * FROM USA_HP;

In this image, the column name has been changed from NAME_HP to NAMES by using the ALTER statement and only the purpose of the SELECT statement is used to retrieve all the records with the column name from the USA_HP table.

Read: How to Create Database in MariaDB

MariaDB Rename Column Name in the Table

In this section, we will learn how to rename column names in the table with the help of the CHANGE clause in the ALTER TABLE statement with the given examples.

First, let’s have a look at the TEACHER_ACTIVITIES table with the following query:

SELECT * FROM TEACHER_ACTIVITIES;

In this query, the SELECT statement retrieves all records of the TEACHER_ACTIVITES table.

The sample example to rename column name in the table in MariaDB is given below:

EXAMPLE:

ALTER TABLE TEACHER_ACTIVITIES CHANGE COLUMN SUBJECT_1 FIRST_SUBJECT VARCHAR(25);

SELECT * FROM TEACHER_ACTIVITES;

In this query, the ALTER TABLE statement has changed the column name SUBJECTS_1 to FIRST_SUBJECT with the help of the CHANGE COLUMN constraint in the TEACHER_ACTIVITIES table. So, to see the process of the ALTER TABLE statement we have used the SELECT statement for the TEACHER_ACTIVITES table.

There is another way to check the ALTER TABLE statement for the CHANGE COLUMN constraint, it can be done by DESC TABLE_NAME statement which is used to describe column names with constraints in the table.

Read: How to create a user in MariaDB

MariaDB Rename Column in Select

In this section, we will learn how to rename the column by using the ALIAS clause in the SELECT statement in the MariaDB with the given syntax and examples.

In MariaDB, the ALIAS clause is used to change the column name for the temporary session in the table for the resultset. The syntax of the ALIAS clause for the rename of the column is given below:

SYNTAX:

SELECT tbl.[* | column_name], expression AS alias_name from table_name tbl;

The syntax explanation:

• tbl.* : It is used as table_name name to carry out the expression or column from the table.
• expression: The old column that need to be changed by using the ALIAS clause.
• AS alias_name: The old column_name changed to new column_name for the temporary session.
• table_name: The table from which data need to be retreived. There should be atleast one table with the FROM clause.

The sample example to rename a column in the SELECT statement is given below:

select tbl.*,teacher_Name AS 'Name of the Teacher' from teacher_activities tbl;

In this query, the SELECT statement retrieve all records from the TEACHER_ACTIVITIES table and it also changed the teacher_name column to the ‘Name of the Teacher’ column by using the ALIAS clause with AS keyword in the teacher_Activities table.

Read: How To Check MariaDB Version

MariaDB Rename Column Error

In this section, we will find an error to rename the column by using the MariaDB ALTER TABLE statement with given examples.

In MariaDB, normally error arises due to syntax error only while writing a query to change the column name to another column_name or using a different datatype with opposite string or number with the ALTER TABLE statement.

The sample example of the MariaDB Rename Column Error is given below:

SYNTAX ERROR EXAMPLE:

ALTER TABLE TEACHER_ACTIVITIES COLUMN COST_1 FIRST_SUBJECT_COST INT;

In this query, we have written the wrong syntax query as the example for the ALTER TABLE statement and to change the column_name old one to new_one like COST_1 to FIRST_SUBJECT_COST column.

The sample example to rewrite the above query with no error is given below:

ALTER TABLE TEACHER_ACTIVITIES CHANGE COLUMN COST_1 FIRST_SUBJECT_COST INT;

SELECT * FROM TEACHER_ACTIVITIES;

In this query, while re-writing the above query correctly. And the ALTER TABLE statement changes the column name from COST_1 to FIRST_SUBJECT_COST with datatype INT. If we want to see the ALTER TABLE statement has been processed properly then we can use the SELECT statement to check all the records with column names in the TEACHER_ACTIVITIES table.

Read: MariaDB Timestamp + Examples

MariaDB Alter Rename Column

In the section, we will use the MariaDB ALTER TABLE statement to rename the column with the help of the CHANGE COLUMN clause with the given syntax and examples.

In MariaDB, the ALTER TABLE statement is used to modify, add, drop/delete columns in a table. It is also used to rename a column in the table. The syntax of the MariaDB Alter Rename Column is given below:

ALTER TABLE table_name
CHANGE COLUMN old_col_name new_col_name 
column_definition
[FIRST | AFTER column_name]

First, let’s have a look at the TEACHER_ACTIVITIES table by using the following query:

SELECT * FROM TEACHER_ACTIVITIES;

In this query, the SELECT statement is used to retrieve all records from the TEACHER_ACTIVITIES table.

The sample example of the MariaDB Alter Rename Column is given below:

ALTER TABLE teacher_activities CHANGE COLUMN SUBJECT_2 SECOND_SUBJECT VARCHAR(25);

SELECT * FROM teacher_activities;

In this query, the ALTER TABLE statement has changed the column name from SUBJECT_2 to SECOND_SUBJECT column as a new column_name with data type VARCHAR(25).

After that, we have used the SELECT statement to retrieve all records from the TEACHER_ACTIVITIES table. And to check the column name has changed or not by using the ALTER TABLE statement.

Read: How to Create Function in MariaDB

MariaDB Rename Column If Exists

In this section, we will learn how to rename the column with the IF EXISTS clause in the MariaDB with the example.

The main purpose of the IF EXISTS clause with the ALTER TABLE statement is to check whether rename of the column is done or not. If the rename of the column is already executed in the table, it will throw an error.

EXAMPLE:

ALTER TABLE teacher_Activities RENAME COLUMN if EXISTS COST_2 TO SECOND_SUBJECTCOST;

SELECT * FROM teacher_activities;

In this query, by using the IF EXISTS clause with the ALTER TABLE statement, it executed for the first time and changed the old column name COST_2 to the new column name SECOND_SUBJECTCOST in the TEACHER_ACTIVITIES table.

After the execution of the ALTER TABLE statement, we have used the SELECT statement to retrieve all records and to check the column name from old to new in the TEACHER_ACTIVITIES table.

Read: How to Create Trigger in MariaDB

MariaDB Rename Primary Key Column

In this section, we will learn how to rename the primary key column with the help of the MariaDB ALTER TABLE statement in the given examples.

Let’s have a first look at the TEACHER_ACTIVITIES table by the following query:

SELECT * FROM TEACHER_ACTIVITIES;

With the help of the SELECT statement, it retrieved all records from the TEACHER_ACTIVITIES table.

The sample example to rename the primary key column in the MariaDB is given below:

ALTER TABLE TEACHER_ACTIVITIES RENAME COLUMN TEACHER_ID TO ID;

SELECT * FROM TEACHER_ACTIVITIES;

In this query, the ALTER TABLE statement has been used to rename the old column name from TEACHER_ID to the new column name of the primary key as ID from the TEACHER_ACTIVITIES table.

If we want, we can use the SELECT statement to check the primary key column has been acquired properly or not in the table or we can use the DESCRIBE TEACHER_ACTIVITIES table.

Also, take a look at some more tutorials on MariaDB.

• MariaDB Temporary Table + Examples
• MariaDB Between + Examples
• How to Drop Column from MariaDB Table
• How to Add Column in MariaDB [5 Examples]
• MariaDB Vs SQL Server – Detailed Comparison
• How to Grant User Access to a MariaDB Database
• MariaDB Transaction

In this MariaDB tutorial, we will study the use of the MariaDB Rename Column statement and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB rename column
• MariaDB rename column name
• MariaDB rename column name in the table
• MariaDB rename column example
• MariaDB rename column in select
• MariaDB rename column error
• MariaDB alter rename column
• MariaDB rename column if exists
• MariaDB rename primary key column