ORACLE SQL Update COMMAND

ORACLE SQL Update COMMAND

How to Update Rows in Oracle SQL 

About the author of this Article
Author of this article is having more than 20 years experience of using Databases and SQL.He had also authored a Book on Oracle.

SQL Update Table Command 

SQL Update Table Command is used to update existing records or rows of a database table. SQL Update Table Command is applied when some changes happens in records. Suppose in case of an employee if he changes his city ,then his record in database must be updated. Another example could be for annual increments in salaries of all the employees, we need update command.

The general syntax of SQL Update Table Command is given below

 Update table name set column name=new value;

SQL Update Table Query

SQL Update Statement Example

Lets have a look on sql update statement example. 

Suppose you want to change present city of employee named Adam to Chandigarh.

SQL>Update employee set ecity=’Chandigarh’ where ename=’Adam’;

Here employee is name of the table to be updated.

SQL Update Where clause

In SQL Update Where Clause, WHERE is optional. If you do not supply WHERE keyword in SQL Update Where Clause then , the update statement will update all the rows of the table.

SQL Update Where clause

Suppose you supply following update statement.

SQL>Update employee set ecity='Nagpur';

In this case cities of all the employees will be updated to Nagpur.

Few more examples to illustrate the use of SQL Update Where Clause.

Change city of employees whose name starts from letter N to NAGPUR
SQL> update employee set ecity='NAGPUR' where ename like 'N%';
Change city of employees whose name doses not starts from letter N

SQL> update employee set ecity='NAGPUR' where ename not like 'N%';

Increment salaries of all the employees by Rs.1000

SQL> update employee set salary=salary+1000;

Increment salaries of all the employees by 10%

SQL> update employee set salary=salary+(Salary*10/100);

Increment salaries of all those employees by 10% whose city is Nagpur

SQL> update employee set salary=salary+(Salary*10/100) where ecity='Nagpur';

How to Update Multiple Records in SQL

To update  multiple records in SQL, if we omit where condition in update statement, then all the rows will be affected by update statement.

How to Update Multiple Records in SQL

Example to show how to update multiple records in SQL : 

update employee set ecity='New Delhi';

in this case city of all the employees will be updated to New Delhi

To update multiple rows, we use where condition for a group of rows too.

Example to show how to update multiple records in SQL :

update employee set ecity='New Delhi' where salary>50000;

In this case city of those employees whose salary is greater than 50000 will be updated to New Delhi.

SQL Update Table Query

You can also use arithmetic operators in update, like increment the salary of all the employees by 100.

The update statement will be like this:

Update employee set salary=salary+100;

SQL Update Multiple Rows at Once

You can update multiple rows at once in SQL. We already discussed about this, to update multiple rows at once, there are many ways.

Suppose you wants to update all the rows of a table using a single update statement.

SQL Update Multiple Rows at Once

In that case don't provide where condition in update statement.

Suppose you supply following update statement at sql prompt

SQL> Update employee set salary=50000;

The above statement will make salaries of all the employees to 50000.

So this was one of the way to update multiple rows at once.

Multiple rows can also be updated at once if the where condition selects multiple rows.

lets us have one MORE example to have an idea of this.

sql> Update employee set salary=50000 where ecity='NAGPUR';

In this case salaried of all those employees will be set to 50000 whose living city is NAGPUR. There is always a possibility that there could be multiple records having living city as NAGPUR.

Similarly you can update any record or any number of records.

We hope all your doubts are cleared about SQL Update Statement after reading this post. We will appreciate social media share of this post by you. This will encourage us to arrange more stuff for you like this in future too.