Trending October 2023 # How Update Statement Works In Pl/Sql? # Suggested November 2023 # Top 10 Popular

You are reading the article How Update Statement Works In Pl/Sql? updated in October 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How Update Statement Works In Pl/Sql?

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax:

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.

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.

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

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.

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.