PL/SQL Update

The Update statement is part of Data Manipulation Language and allows the user to update a single record or multiple records in a table.

PL/SQL update statements can be used to update data in a database table. You can update a single row, multiple columns, or all rows in a table. You can also use the PL/SQL update statement with a join or with a subquery.

Syntax:

UPDATE table SET column1 = new_value1;

UPDATE table SET 
	column1 = value1, 
	column2 = value2, 
	column3 = value3, ... 
WHERE condition;

You can also use the PL/SQL update statement with a subquery. For example, to update the name of all customers in the customer table who have an order in the orders table, you would use the following PL/SQL update statement:

UPDATE customer c
SET name = 'Tom'
WHERE customer_id IN (SELECT customer_id FROM orders);

When using PL/SQL update statements, you can use PL/SQL variables to update data in a database table. For example, to update the name of a customer in the customer table, you would use the following PL/SQL update statement:

DECLARE
v_name VARCHAR2(100) := 'Tom';
BEGIN
UPDATE customer
SET name = v_name
WHERE customer_id = 10;
END;
/

PL/SQL update statements can also be used to update multiple columns in a database table. The following PL/SQL update statement updates the name and address of a customer in the customer table:

UPDATE customer SET 
name = 'Tom', 
address = '123 Main Street' 
WHERE customer_id = 1;

PL/SQL update statements can also be used to update all rows in a database table. The following PL/SQL update statement updates the name of all customers in the customer table:

UPDATE customer SET name = 'John'; 

PL/SQL update statements can also be used with a join to update data in multiple database tables. The following PL/SQL update statement updates the name of a customer in the customer table and the name of the customer’s orders in the orders table:

UPDATE customer c 
JOIN orders o ON c.customer_id = o.customer_id 
SET c.name = 'John Doe', o.name = 'John Doe' 
WHERE c.customer_id = 1; 

PL/SQL update statements can also be used with a subquery to update data in a database table. The following PL/SQL update statement updates the name of all customers in the customer table who have an order in the orders table:

UPDATE customer c 
SET name = 'John Doe' 
WHERE customer_id IN (SELECT customer_id FROM orders);

Simple Update example

UPDATE employees SET salary = 100;

UPDATE employees 
SET salary = 2000, 
	city_name='London' 
WHERE dept_id=10;

Complex Update example

UPDATE employees e
   SET e.city_id = (SELECT c.city_id 
					FROM cities c 
					WHERE c.city_name='London')
 WHERE EXISTS (SELECT d.dept_id 
				FROM departments d 
				WHERE d.dept_id=e.dept_id 
				AND d.dept_id=10);