Back to: Oracle Tutorials for Beginners and Professionals
Assignment Operators in Oracle with Examples
In this article, I am going to discuss Assignment Operators in Oracle with Examples. Please read our previous article, where we discussed Arithmetic Operators in Oracle with Examples.
Assignment Operator in Oracle
The Assignment Operator in Oracle is used to assign or compare a value to a column or a field of a table. The equal sign (=) is the assignment operator where the value on the right is assigned to the value on the left. It is also used to establish a relationship between a column heading and the expression that defines the values for the column.
Example to Understand Assignment Operator in Oracle:
We are going to use the following Employee table to understand the need and use of the Assignment Operator in Oracle.
Please execute the below SQL query to drop the existing Employee table and Create a new Employee table with the required sample data. We also set the LINESIZE to get the output in the above format.
SET linesize 300; DROP Table Employee; CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(15), Department VARCHAR(10), Salary NUMBER(8, 2), Gender VARCHAR(10), Age INT, City VARCHAR(10) ); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');
Example: Update the Price of each product by adding 1000
Our requirement is to increase the salary of all employees by 1000. Now we will update the Salary column of the Employee table by using the equals operator as an assignment. Following is the SQL statement.
UPDATE Employee SET Salary = Salary + 1000;
Once you execute the above Update SQL statement, you will get the following message saying that 10 rows are updated as shown in the below image.
Now you can verify that the Salary column value in the Employee table is updated as shown in the below image. SELECT * FROM Employee; will give you the following result set.
Here, you can observe the Salary column has been updated by raising the existing salary by adding 1000. Also, you can use the same assignment operator for comparing values. Let us understand this with an example.
UPDATE Employee SET Salary = Salary * 1.02 WHERE ID = 1006;
Once you execute the above Update SQL statement, you will get the following message saying that 1 row is updated.
Now you can verify that the Salary column value in the Employee whose id is 1006 updated as shown in the below image. SELECT * FROM Employee; will give you the following result set.
Here we are updating the Salary column of the Employee table where the Id is 1006. And you can observe that only the Salary with Id =1006 has been updated.
This operator is used for the equality test. Used to test the equality of two operands. For example, display the details of Employees whose Gender is Male.
SELECT * FROM Employee WHERE Gender = ‘Male’;
Once you execute the above Update SQL statement, you will get the following result set displaying only the Male employee’s data.
In the next article, I am going to discuss Relational Operators in Oracle with Examples. Here, in this article, I try to explain Assignment Operator in Oracle with Examples and I hope you enjoy this Assignment Operators in Oracle with Examples article.