Updated April 5, 2023
Definition of PL/SQL UPDATE Statement
PL/SQL provides the update functionality to the users; the update command is used to modify the content of columns in an existing table. The update command uses the SET clause to specify the column name for modification and assign a new value for that field when we use the default, so, at that time, we need to use the default keyword. The update command also uses a where clause to specify conditions for the update. Basically, there are two ways to perform the update command that is traditional, and another is we can perform an update command on one table with records from another table.
Syntax:
There are two different syntax updates in PL/SQL as follows.
update specific table name set colm name 1 = new assigned value, colm name 2 = new assigned value,........... colm name N = new assigned value
where [Expression.......] ;Explanation
In the above syntax, we use the update command with different parameters as follows.
- specified table name: specified table name means an actual name that we need to modify the data.
- colm name: It is a column name from the specified table with a new value that we need to update, so in this way, we can write multiple column names as per requirement.
- set: It is the keyword used to set new values to specific columns.
- where: where clause to specify the condition for the update command
Let’s see the second syntax as follows.
update specific table name set colm name 1 = (select required expression from specified table name 2 where expression) where[expression];Explanation
This is the second syntax for the update command; this syntax is useful when we need to update records from another table. In the above syntax, we use an update command with different parameters as follows.
- colm name: It is the actual column name that we need to update.
- set: It is a keyword used to set new values to specific columns.
After that, we use a subquery, in which we specify the column name with specified table name 2 with where condition as shown.
How does the update statement work in PL/SQL?
Now let’s see how the update statement works in Pl/SQL as follows.
Basically, there are two different ways to perform the update command. Let’s see the traditional way as follows.
Basically, in the traditional way, we need exiting data or tables to perform update commands in PL/SQL. For update purposes, we need different clauses such as SET, Where and we can use them as per our requirement. Normally the update command is used for a single column, or we can use it for multiple column updates as per user requirements.
In a second way, we need one more table with different records; then, we need to perform the update command with a sub-query. In this method, we can update records from another table with where clause, the where clause basically used to specify which row of the table we need to update.
Examples
Now let’s see the different examples of update commands in PL/SQL as follows. First, we need to create a new table by using the create table command as follows.
create table studentA(stud_id number(10) not null, stud_name varchar2(30) not null, stud_city varchar2(30));Explanation
By using create table statement, we created a new table name, studentA, with different attributes and different data types. The final output of the above statement we illustrated by using the below screenshot is as follows.
Now insert some records by using insert into the statement as follows.
insert into studentA(stud_id, stud_name, stud_city) values(101,'Jenny','Mumbai');
insert into studentA(stud_id, stud_name, stud_city) values(102,'Johan','Mumbai');
insert into studentA(stud_id, stud_name, stud_city) values(103,'Pooja','London');
insert into studentA(stud_id, stud_name, stud_city) values(104,'Sameer','London');
insert into studentA(stud_id, stud_name, stud_city) values(105,'Rohit', 'London');
select * from studentA;Explanation
By using the above statement, we inserted five records as shown in the below screenshot as follows.
Now perform the update command.
Suppose we need to update the student city of Jenny at that time; we can use the following statement as follows.
update studentA set stud_city='Pune' where stud_id=101;Explanation
In the above example, we use the update command; here, we need to update the city of Jenny Pune instead of Mumbai by using stud_id. One more thing is that here we update only a single column. The final output of the above statement we illustrated by using the below screenshot is as follows.
Now let’s see how we can update multiple columns as follows.
update studentA set stud_city='Hongkong', stud_name='Virat' where stud_id=102;Explanation
By using the above statement, we updated two columns because, in my table, there are two columns, but in your case, we updated more than two columns. Here stude_id 102 is duplicated, so that is the reason it updated the row. The final output of the above statement we illustrated by using the below screenshot is as follows.
Now let’s see how we can update records from another table as follows.
First, we need to create the new table by using the create table statement as follows.
create table deptB(dept_id number(10) not null, dept_name varchar2(30), stud_id number(10) not null);Explanation
By using the above statement, we created a new table that deptB as shown, and we insert some records into the deptB. The final output of the above statement we illustrated by using the below screenshot is as follows.
Now perform the update command as follows.
update studentA set stud_name = (select dept_name from deptB where deptB.stud_id = studentA.stud_id) where exists (select dept_name from deptB where deptB.stud_id = studentA.stud_id);Explanation
In the above example, we updated records from another table, where we need to update studentA table from the deptB table, so that is why we need to build some relation between these two tables. That means we need a common column in both tables then, and then we can perform the update. The final output of the above statement we illustrated by using the below screenshot is as follows.
See in the above screenshot we updated stud_name from deptB table, here we updated depart name instead of stud_name for understanding purpose, in this way we can update any column name as per our requirement.
Conclusion
We hope from this article you learn PL/SQL update. From the above article, we have learned the basic syntax of updates, and we also see different examples of updates. From this article, we learned how and when we use PL/SQL updates.
Recommended Articles
We hope that this EDUCBA information on “PL/SQL UPDATE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
