Update Statement in PL/SQL

Update Statement in PL/SQL
By  Thomas Mapother February 24, 2011 UPDATE statement is used to change existing values in a table or in the base table of a view or the master table

Similar ArticlesMost ReadTop RatedLatest

Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view. It is used to manipulation and it modifies the rows, the WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected. Before executing an UPDATE statement, Oracle marks an implicit savepoint. If the statement fails, Oracle rolls back to the savepoint.

Syntax for the UPDATE Command:

UPDATE table_name
set (column_name = value)
WHERE statement

Example:

First Create two Tables, Named Wholesaler and Consumer:

SQL> CREATE TABLE Wholesaler
2  ( Wholesaler_id number(10) not null,
3    Wholesaler_name varchar2(50) not null,
4    city varchar2(50),
5    CONSTRAINT Wholesaler_pk PRIMARY KEY (Wholesaler_id)
6  );

Table created.

SQL> CREATE TABLE Consumer
2  ( Consumer_id number(10) not null,
3    Consumer_name varchar2(50) not null,
4    city varchar2(50),
5    CONSTRAINT Consumer_pk PRIMARY KEY (Consumer_id)
6  );
Table created.

Insert Values into the Tables:

SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5001, 'Microsoft', 'New York');
1 row created.
SQL>
SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5002, 'IBM', 'Chicago');
1 row created.
SQL>
SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5003, 'Red Hat', 'Detroit');
1 row created.
SQL>
SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5005, 'NVIDIA', 'LA');
1 row created.
---------------------------------------------------------------------------------------
SQL> INSERT INTO Consumer (Consumer_id, Consumer_name, city)
2  VALUES (7001, 'Microsoft', 'San Francisco');
1 row created.
SQL>
SQL> INSERT INTO Consumer (Consumer_id, Consumer_name, city)
2  VALUES (7002, 'IBM', 'Toronto');
1 row created.
SQL>
SQL> INSERT INTO Consumer (Consumer_id, Consumer_name, city)
2  VALUES (7003, 'Red Hat', 'Newark');

1 row created.

Update the Values:

SQL> UPDATE Wholesaler
2  SET city = ( SELECT customers.city
3  FROM customers
4  WHERE customers.customer_name = Wholesaler.Wholesaler_name)
5  WHERE EXISTS
6    ( SELECT customers.city
7      FROM customers
8      WHERE customers.customer_name = Wholesaler.Wholesaler_name);  3 rows updated.

Final Output after updating the values:
Wholesaler_IDWholesaler_NAMECITY

Happy Learning

Login to add your contents and source code to this article C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.

View the original article here