Modifying data – Update Command

The UPDATE command is used to modify or change one or a group of rows at a time. UPDATE statement have 2 clauses – UPDATE clause followed through a SET clause and an optional 3rd clause – the WHERE clause. The WHERE clause specifies a conditon, that is optional.

UPDATE statement permits the user to specify the table name for that the rows are to be modified. SET clause sets the values of one or more columns as specified through the user. UPDATE statement without a WHERE clause updates all the records in the table.

Syntax:

UPDATE <tablename>
SET <col1>=value,<col2>=value,.. WHERE <condition>

Example:

Consider the table employee2; add a column known as deptno to this table as shown as follows. ALTER TABLE employee2 ADD deptno NUMBER;
Before the update command is issued on issuing. SELECT * FROM employee2;
This display will be:

There are no values in the deptno column. Because INSERT is used to insert new records and UPDATE performs the value updation. Now, suppose the following UPDATE statement
UPDATE employee2
SET deptno=10;
That statement updates all the records of employee table with deptno as 10 and displays the number of records that were changed. Here the display will be,
4 rows updated.

Example:

UPDATE employee2
SET deptno=20 WHERE empno >=1003;
The column of deptno is updated for all the records which satisfy the condition specified in the

WHERE clause. After this command is issued the select statement for this table would yield, SELECT * FROM employee2;

To update more than one column, separate the columns with a ‘,’ as described below

UPDATE employee1

SET salary=6000,doj=’09-SEP-2000’ WHERE doj is NULL;
Here, the salary is updated to 6000 and the doj is changed to 09-SEP-2000 for employees whose the date of joining is NULL. After the table updation, the selection from the table would yield,