Home » MySQL

Difference between ALTER and UPDATE Commands in MYSQL

Learn about the MySQL ALTER and UPDATE command, and what's the differences between these commands.
Submitted by Apurva Mathur, on November 08, 2022

MySQL is simply a database management system, the two terms which are important here is database and management. So, the database is a collection of data, we can assume it is a hard copy file but when it comes to manage the data electronically the database comes into the picture. And, the other term is management which means a way to store that crucial data.

ALTER and UPDATE both commands are used for updating some particular thing. ALTER commands are used to modify the structure of a table whereas UPDATE commands are used to modify the result inside the table.

Let us see both commands in detail.

ALTER COMMAND

ALTER command comes under DDL or Data Definition Command. When any command comes under DDL commands that directly means that, the command will deal with the database descriptions and table creations. These are the commands to which a normal user/customer can not have access, these commands will be straight used by the developer via the particular application.

ALTER command simply help us to update the structure of the table. When we define a table we specify the database name, column name, column definitions, constraints, etc to update all these things ALTER command is created. The ALTER command can modify database name, column name, column definitions, etc.

Let us see the different case scenarios to use ALTER command.

Case 1: suppose you have created a table inside that table you have 5 columns and you want to add one more column to this table then with the help of ALTER command we can easily do this.

To simply add any column in an existing table, ALTER command is used with ADD keyword. Here is the syntax:

ALTER TABLE table_name
ADD field_name data type;

Now let us see the practical demonstration of this case,

Here In the example given below, I have 5 columns named:

And now if I want to add one more column named "Total_marks" then I will use the ALTER command in the following manner;

ALTER TABLE students ADD Total_marks int;

After hitting this query our result will be displayed as:

As you can see, one more column is added to the table.

Case 2: Suppose I have to change the column name "Major_subjects" to "Subjects" then in such case we can use ALTER command by using the following syntax:

ALTER TABLE table_name
CHANGE COLUMN 'old_field_name' 'new_field_name' data type;

According to the case, our query will be:

ALTER TABLE students 
CHANGE COLUMN `Major_Subjects` `Subjects` VARCHAR(100) NULL DEFAULT NULL ;

Our result would be displayed as,

Case 3: Suppose you have to remove/drop any column then to perform this task we will the following ALTER command syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

The query given below will remove/drop the column named "Total_marks".

TABLE students DROP COLUMN Total_marks;

Case 4: Suppose you want to rename any column, then we will use the following syntax:

ALTER TABLE old_table_name
RENAME TO new_table_name;

UPDATE COMMAND

It is one of the basic operations which we perform in MYSQL. It comes under the DML command i.e., Data Manipulation Language.

As the name suggests, this command helps us to update/modify the record in a table. So basically, updating a record simply means if the user by mistake fills in any wrong information and now if the user wants to change it so it should just update the previous value which was filled by the user despite creating a new record.

For an instance, let us take the example of a famous social media platform named Facebook. The very first thing you do when you go to some social media platform is the registration to the platform, which mainly asks for your name, phone no, email, etc, after successful registration, at the backend your information is saved.

Now for some reason, if you want to change the phone number, then you just simply update your information. Ever thought about how did this happen? This happens with the help of the UPDATE command; it simply saves the new information provided by the user at the backend by replacing the old one.

The UPDATE commands are widely used commands, we can see the applications of update commands on e-commerce websites, MIS platforms, social media platforms, and many more.

Syntax to update any record

UPDATE table_name
SET field1 = value1, field2 = value2, ...
WHERE conditions;

Now let us just have a look at how we can update a record:

Suppose I have a table named "students", and inside this table, I have 3 records,

Now we will see different scenarios to update a value in the above table,

Case 1: UPDATING A SINGLE RECORD

If I want to update only one record, let's just take I only want to update the phone number of the student named "kruti" then in such case, I will write my query as,

UPDATE students
 SET `Phoneno` = '99231769' 
WHERE (`ID` = '3');

Output:

Case 2: UPDATING MORE THAN ONE COLUMN

if I want to change the record of ID=1 then I can simply do this by writing the following query,

UPDATE students  
SET 
`Name` = 'suruchi', 
`Phoneno` = '76997702', 
`Emailid` = '[email protected]', 
`Major_Subjects` = 'PCMB' 
WHERE (`ID` = '1');

Output:

---