Reset MySQL field to default value?

In MySQL, there are two approaches by which you can reset the MySQL field to default value. One is default keyword and another is default() function.

Case 1: Using default keyword. The syntax is as follows:

UPDATE yourTableName SET yourColumnName=default where yourCondition;

Case 2: Using default() function. The syntax is as follows:

UPDATE yourTableName SET yourColumnName=default(yourColumnName) where yourCondition;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table Default_Demo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> Age int,
   -> Salary float,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.73 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into Default_Demo(Name,Age,Salary) values('John',23,405.56);
Query OK, 1 row affected (0.18 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Carol',25,1000.98);
Query OK, 1 row affected (0.22 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Larry',21,987.24);
Query OK, 1 row affected (0.09 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Sam',24,986.10);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('Mike',22,10000.50);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Default_Demo(Name,Age,Salary) values('David',26,100.45);
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from Default_Demo;

The following is the output:

+----+-------+------+---------+
| Id | Name  | Age  | Salary  |
+----+-------+------+---------+
|  1 | John  |   23 | 405.56  |
|  2 | Carol |   25 | 1000.98 |
|  3 | Larry |   21 | 987.24  |
|  4 | Sam   |   24 | 986.1   |
|  5 | Mike  |   22 | 10000.5 |
|  6 | David |   26 | 100.45  |
+----+-------+------+---------+
6 rows in set (0.00 sec)

Here is the query to reset the MySQL field to default values.

Case 1: Using default keyword. The query is as follows:

mysql> update Default_Demo set Age=Default where Id=6;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now you can check the table record, the column Age is NULL where Id is 6. The query is as follows:

mysql> select *from Default_Demo;

The following is the output:

+----+-------+------+---------+
| Id | Name  | Age  | Salary  |
+----+-------+------+---------+
|  1 | John  |   23 | 405.56  |
|  2 | Carol |   25 | 1000.98 |
|  3 | Larry |   21 | 987.24  |
|  4 | Sam   |   24 | 986.1   |
|  5 | Mike  |   22 | 10000.5 |
|  6 | David | NULL | 100.45  |
+----+-------+------+---------+
6 rows in set (0.00 sec)

Look at the Id 6 in which Age column has been updated with default value NULL.

Case 2: Now you can also use default() function. Here, update the Salary column with default value where Id is 6. The query is as follows:

mysql> update Default_Demo set Salary=Default(Salary) where Id=6;
Query OK, 1 row affected (0.21 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now check the table record where Id is 6.

mysql> select *from Default_Demo where Id=6;

The following is the output:

+----+-------+------+--------+
| Id | Name  | Age  | Salary |
+----+-------+------+--------+
|  6 | David | NULL | NULL   |
+----+-------+------+--------+
1 row in set (0.00 sec)

Look at the salary column the default value NULL is updated successfully.