Setting similar value for a column in a MySQL table?

MySQLMySQLi Database

You can set value for a column of all records with the help of update command.

The syntax is as follows if you want set NULL value for all the records in a column −

update yourTableName set yourColumnName = NULL;

Or if you want to use empty string, the following is the syntax −

update yourTableName set yourColumnName = ’’;

To understand the above concept, let us create a table. The query to create a table.

mysql> create table StudentDemo
   −> (
   −> Studentid int,
   −> StudentName varchar(100),
   −> Age int
   −> );
Query OK, 0 rows affected (0.64 sec)

The following is the table to insert records −

mysql> insert into StudentDemo values(1,'Johnson',23);
Query OK, 1 row affected (0.18 sec)

mysql> insert into StudentDemo values(2,'Carol',24);
Query OK, 1 row affected (0.16 sec)

mysql> insert into StudentDemo values(3,'David',20);
Query OK, 1 row affected (0.18 sec)

mysql> insert into StudentDemo values(4,'Bob',21);
Query OK, 1 row affected (0.19 sec)

Display all records from the table with the help of select statement −

mysql> select *from StudentDemo;

The following is the output −

+-----------+-------------+------+
| Studentid | StudentName | Age |
+-----------+-------------+------+
|         1 | Johnson     | 23   |
|         2 | Carol       | 24   |
|         3 | David       | 20   |
|         4 | Bob         | 21   |
+-----------+-------------+------+
4 rows in set (0.00 sec)

Here is the query to set a column value to NULL for all records in a specific column. The query is as follows −

mysql> update StudentDemo set Age=NULL;
Query OK, 4 rows affected (0.14 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Let us check now −

mysql> select *from StudentDemo;

The following is the output displaying that we have successfully updated the “Age” column to NULL −

+-----------+-------------+------+
| Studentid | StudentName | Age |
+-----------+-------------+------+
|         1 | Johnson     | NULL |
|         2 | Carol       | NULL |
|         3 | David       | NULL |
|         4 | Bob         | NULL |
+-----------+-------------+------+
4 rows in set (0.00 sec)
raja
Published on 11-Jan-2019 10:13:54
Advertisements