How to Set a Column Value to Null in PL/SQL?

In PL/SQL, setting a column value to NULL is a common requirement when working with databases. Understanding how to accomplish this task is essential for database developers and administrators. In this article, we will delve into the concept of setting a column value to NULL in PL/SQL, covering the syntax, examples, and output explanations.

• PL/SQL: A procedural language extension for SQL by Oracle.
• NULL: Represents a missing or unknown value in a database.
• Database Management: A crucial skill for developers and administrators.

Setting a Column Value to NULL in PL/SQL

To set a column value to NULL in PL/SQL, the UPDATE statement is used. The syntax for updating a column to NULL is as follows:

UPDATE table_name
SET column_name = NULL
WHERE condition;

Here,

• UPDATE: SQL keyword used to modify or change existing records in a table.
• table_name: The name of the table that you want to update.
• SET: SQL keyword indicating the column you want to modify.
• column_name: The specific column within the table that you want to update.
• NULL: Set the value of the specified column to NULL.
• WHERE: SQL keyword used to filter the rows that will be updated.
• condition: The criteria that the rows must meet to be updated. If omitted, all rows will be updated.

Examples and Output Explanation

Example 1 : Let’s Say We Have a Table Called “Employee” with the Following Data

“We can create the ‘Employee’ table using the following SQL code, which defines the table structure with columns such as ‘Emp_ID,’ ‘Emp_Name,’ ‘Salary’, and ‘department_Id,’ specifying appropriate data types for each column to store information about Employee’ details.”

CREATE TABLE Employee (
    Emp_ID INT PRIMARY KEY,
    Emp_Name VARCHAR(255),
    Salary DECIMAL(10, 2),
    department_Id INT
);

INSERT INTO Employee (Emp_ID, Emp_Name, Salary, department_Id)
VALUES (101, 'John Doe', 50000.00, 1),
(102, 'Jane Smith', 60000.00, 2),
(103, 'Robert Johnson', 55000.00, 1),
(104, 'Emily Davis', 48000.00, 2);

Employee Table

And we have to set the salary to NULL for an employee with Emp_id 101, the following query can be used:

UPDATE Employee
SET Salary = NULL
WHERE Emp_Id = 101;

Output:

Output for Example1

We can observe that the salary for the employee with ‘Emp_Id’ 101 is set to NULL.

Example 2 : Let’s Say We Have a Table Called “Students” With the Following Data

“We can create the ‘Students’ table using the following SQL code, which defines the table structure with columns such as ‘Students,’ ‘Subject,’ and ‘Marks,’ specifying appropriate data types for each column to store information about students’ marks in different subjects.”

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50), 
    Date_of_Birth DATE,
    Major VARCHAR(100),
    GPA DECIMAL(3, 2)
);

INSERT INTO Student (Student_ID, Name, Date_of_Birth, Major, GPA)
VALUES
    (1, 'John Doe', '2000-05-15', 'Computer Science', 3.75),
    (2, 'Jane Smith', '2001-02-28', 'Mathematics', 3.90),
    (3, 'Michael Johnson', '1999-11-10', 'Physics', 3.60),
    (4, 'Emily Williams', '2000-08-05', 'Biology', 3.80);

Student Table

And we have to set the GPA to NULL for an Student with Student_ID 4, the following query can be used:

UPDATE Student
SET GPA = NULL
WHERE Student_ID = 4;

Output:

Output for Example2

We can observe that the GPA for the Student with ‘Student_ID’ 4 is set to NULL.

Conclusion

in conclusion, Setting a column value to NULL in PL/SQL is accomplished through the utilization of the UPDATE statement, employing the correct syntax. This process is integral for upholding the precision and consistency of data within databases. The term “Data Accuracy” refers to the assurance of correctness in database records, ensuring that information is reliable and error-free. Simultaneously, “Data Integrity” is the practice of maintaining the reliability and consistency of data throughout its lifecycle. Proficiency in this concept is imperative for conducting efficient database operations, safeguarding data accuracy, and ensuring the integrity of the information stored. As you delve into PL/SQL projects, applying this knowledge will be essential for the effective management of databases and the preservation of data integrity.