How to Set a Column Value to NULL in MariaDB

In MariaDB, the NULL represents an unknown value in a column. Changing a column value to NULL is the most common operation performed in MariaDB that allows us to remove existing data in a specific field. It is applicable in different ways including data correction, record inclusions and values setting. In this article, we will look through the different methods with which we can set a column value to NULL in MariaDB.

Set a Column Value to NULL in MariaDB

The fundamental syntax to set a column value to NULL is as follows.

Syntax:

UPDATE table_name
SET column_name = NULL
WHERE condition;

Explanation:

• table_name: Name of your table.
  
• column_name: Name of the column you want to set null.
  
• WHERE condition: Condition with any necessary criteria to identify the records you want to update.

Method to Set a Column Value to NULL in MariaDB

Method 1: Using UPDATE Statement

First let's create a table and insert data into it.

Create a Table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
Hire_date DATE
);

Insert Data:

INSERT INTO employees VALUES
(1, 'Kavya', 'Sharma', 'Finance', 50000.00,'2012-01-15'),
(2, 'Vardhana', 'Sharma', 'IT', 60000.00,'2022-02-20'),
(3, 'Bob', 'Johnson', 'Sales', 75000.00,'2019-03-10'),
(4, 'Vivek', 'Sharma', 'IT', 65000.00,'2022-02-20'),
(5, 'Minal', 'Pandey', 'Sales', 70000.00,'2022-03-10');

Output:

Example 1:

Suppose we want to set to NULL for all employees who hired before 2020:

Query:

UPDATE employees SET salary = NULL WHERE Hire_date < '2020-01-01';
SELECT * FROM employees;

This query will delete salary details from the database for all employees who were hired before 1 January 2020, by setting the salary value to NULL. This operation is often utilized for such purposes as salary records updating or data cleaning.

Output:

Example 2:

If you want to set column value NULL for employees who belongs to IT department.

Query:

UPDATE employees set department =  NULL WHERE department = 'IT';
SELECT * FROM employees;

Output:

Explanation: The execution of this query will remove the department assignment for all employees who are part of the 'IT' department by updating their department values to NULL. This operation can be helpful for activities like relocating staff to different departments or for cleaning up data.

Example 3:

Set a column value null without specifying where condition.

Query:

UPDATE employees SET last_name = NULL;
SELECT * FROM employees;

Output:

Explanation: After running this query you are actually deleting the last names for all the employees in the employees table, leaving the last_name column with NULL values for each record. You should be careful while executing this operation, as it permanently erases the data in the last_name column with no chance to revert the changes unless you have a backup of the data.

Method 2: Using ALTER Statement

If we want to set the default value of a column to NULL or if we want to allow NULL values in a column that previously did not allow them then we can use the ALTER TABLE statement.

Query:

ALTER TABLE table_name MODIFY column_name datatype NULL;

Example 1:

Suppose in our employees table we want to allow null values for the Hire_date column.

Query:

ALTER TABLE employees MODIFY Hire_date DATE NULL;

Output:

Explanation: The query is asking MariaDB to change the employees table by modifying the Hire_date column to DATE and making that column allow NULL values. The structure of the table would consequently change which may affect how the data in the Hire_date column is stored and retrieved.

Conclusion

In MariaDB the task of setting the value of a column to NULL is a simple process. It can be performed using either the UPDATE statement or the ALTER TABLE statement. According to your particular preferences, you can make selection the method that suits your needs. Irrespective of whether you’ll be updating existing entries or modifying table columns, MariaDB grants you the required tools to smoothly handle NULL values in your database.