Updating multiple columns in a table

How to update Multiple Fields in a table with One UPDATE Statement?

Some times we want to change values of multiple columns in a table for one or more rows.
In Oracle SQL the UPDATE is capable to update as many many rows as you want to update, and also you can update multiple columns in a single UPDATE statement.
This means for updating multiple rows you need to specify the where condition such as it evaluates true for the rows to be updated, but for updating multiple columns you need to use multiple column = value clauses in the UPDATE statement to change as many fields, using comma as a separator.

For example if you want to change the last_name, job_id and salary of kamlesh, employee_id 1001, we can use a single update statement as below :


UPDATE employees
SET last_name = 'Khaliya', job_id = 'IT_PROG', salary = salary * 1.5
WHERE employee_id = 1001;

Let's have idea about, how It Works:

In this example, Oracle's SQL engine will first targets the row with the employee_id of 1001. It then changes each field based on the SET criteria given. It performs this in one pass of the row.

In Oracle we can also perform updates for multiple columns by making groups, using parenthesis, where we can update the whole group by selecting the data using sub-queries. To perform above update as group we can write the statement like :

update employees
set (last_name,job_id,Salary)
= (select 'Khaliya','IT_PROG',salary * 1.5 from dual)
where employee_id = 1001;

In such cases we can perform the updates with the data of other tables also, for that we use some table name instead the dual.
It is very important to remember that if the sub-query returns no data, the values specified in the set clause will be set to null. And in group update, you always need to use a sub-query, you can't write like

(last_name,job_id,Salary) = ('Khaliya','IT_PROG',salary * 1.5)

this will raise an error.
There must be one to one relation ship between the group and the select data, and the data type must be compatible.