Assignment Operators in Oracle

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.

Example to Understand 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.

Update the Price of each product by adding 1000

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.

Assignment Operators in Oracle with Examples

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.

Assignment Operators in Oracle

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.

Assignment Operators in Oracle

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.

Assignment Operator in Oracle with Examples

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.

Leave a Reply

Your email address will not be published. Required fields are marked *