Modifying and Updating Tables in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about altering Table Commands in MySQL with examples.
 

MODIFYING

 
The modify command is used when we have to modify a column in the existing table, like add a new one, modify the datatype for a column, and drop an existing column. By using this command we have to apply some changes to the result set field. This command allows more or fewer characters than before.
 
To understand the concept of MySQL ALTER TABLE Commands, let’s take a sample database and a table. So, without wasting time, let’s start.
  1. CREATE DATABASE CsharpCorner;  
  2.   
  3. USE CsharpCorner;  
  4.   
  5. CREATE TABLE CsharpCorner_MVPs(  
  6.   MVPID INT AUTO_INCREMENT PRIMARY KEY,  
  7.   MVPName VARCHAR(100) NOT NULL,  
  8.   MVPForYear INT NOT NULL,  
  9.   MVPAddress VARCHAR(100)  
  10. ); 

1) Add a new column to the existing table

 
ALTER TABLE ADD COLUMN statement is used to add one or more new columns to an existing table.
 
a) Add a single column to a table
 
Syntax
Alter table <table_name>
Add column <new_column_name> Datatype [CONSTRAINTS];
 
Example
  1. ALTER TABLE csharpcorner_mvps  
  2. ADD COLUMN MVPKitStatus INT
Note:
Use the “DESCRIBE” command to check the result from the ALTER TABLE ADD statement.
 
 
b) Add multiples column to a table
 
Syntax
Alter table <table_name>
Add column <new_column_name1> Datatype [CONSTRAINTS],
Add column <new_column_name2> Datatype [CONSTRAINTS]…
Add column <new_column_nameN> Datatype [CONSTRAINTS];
 
Example
  1. ALTER TABLE csharpcorner_mvps  
  2. ADD COLUMN Country VARCHAR(50),  
  3. ADD COLUMN Description VARCHAR(250); 
Note:
Use the “DESCRIBE” command to check the result from the ALTER TABLE ADD statement.
 
 

2) MODIFY the Column(s) in a Table

 
ALTER TABLE MODIFY statement is used to modify one or more columns to an existing table.
 
a) Modify a single column in a table
 
Syntax
ALTER TABLE <table_name>
MODIFY <column_name> Datatype;
 
Example
  1. ALTER TABLE csharpcorner_mvps  
  2. MODIFY MVPKitStatus VARCHAR(100); 
Note:
Use the “DESCRIBE” command to check the result from the ALTER TABLE MODIFY statement.
 
 
b) Modify multiple columns in a table
 
Syntax
ALTER TABLE <table_name>
MODIFY <column_name1> Datatype,
MODIFY <column_name2> Datatype;
 
Example
  1. ALTER TABLE csharpcorner_mvps  
  2. MODIFY MVPAddress VARCHAR(250) NOT NULL,  
  3. MODIFY MVPKitStatus VARCHAR(100) NOT NULL,  
  4. MODIFY Description VARCHAR(221); 
Note:
Use the “DESCRIBE” command to check the result from the ALTER TABLE MODIFY statement.
 
 

3) DROP a Column

 
ALTER TABLE DROP COLUMN statement is used to drop the column(s) in a table.
 
Syntax
ALTER TABLE <table_name>
DROP COLUMN <column_name>;
 
Example
  1. ALTER TABLE csharpcorner_mvps  
  2. DROP COLUMN Country;  
Note:
Use the “DESCRIBE” command to check the result from the ALTER TABLE DROP statement.
 
 

4) Change Column name

 
To rename a column in a table, use the following statement.
 
Syntax
ALTER TABLE <table_name>
CHANGE <old_column_name> <new_column_name] Datatype;
 
Example
  1. ALTER TABLE csharpcorner_mvps  
  2. CHANGE Description MVPDescription VARCHAR(221); 
Note:
Use the “DESCRIBE” command to check the result from the ALTER TABLE CHANGE statement.
 
 

UPDATING STATEMENT

 

A) UPDATE QUERY

 
It is necessary to change the data in our database, so these commands are helpful for updating a table.
 
Syntax
UPDATE <table_name> SET <column_name> = <new_column_value>
WHERE [condition(s)];
 
Example
  1. UPDATE csharpcorner_mvps  
  2. SET MVPAddress = 'sec 63, Noida'  
  3. WHERE MVPID = 3; 
Note:
Use the “DESCRIBE” command to check the result from the UPDATE query.
 
 

B) DELETE STATEMENT

 
Syntax
DELETE from [table_name]
WHERE <condition>;
 
Example
  1. DELETE from csharpcorner_mvps  
  2. WHERE MVPID = 3; 
 

C) Update database permissions/privilages

  1. FLUSH PRIVILEGES

D) Load a CSV file into a table

 
LOAD DATA INFILE '/tmp/filename.csv' replace
 
INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3);
 

CONCLUSION

 
In this article, I have discussed the concept of modifying and updating Tables in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!