SQL UPDATE Query

Quite often it is required to make changes or modifications in the records of the table, so in order to make these changes, the UPDATE statement is used. With this statement, the user can modify the existing data stored in the table. It can update zero or more rows in a table. To update rows in table, it must be in your own schema or you must have update privilege on the table.

Here, <tablename> represents the name of the table, <colurnnname> (1 to n) corresponds to the columns in the table to be modified, <expression> corresponds to the valid SQL expression. The [where condition] restricts the rows updated to those for which the specified condition is true, it is optional. If [where condition] is omitted, Oracle updates all the rows in the table. The where condition can include comparison operators (=, <,> etc.), logical operators (AND, OR) etc.

The syntax is
 
UPDATE <tablename> 
SET <columnname1> = <expression> [, <columnnarne2>= <expression> ] 
........... [,<columnnameN>=<expression> ] 
[WHERE condition]; 

Suppose that we insert a column SALARY (Number (8, 2)) into the INSTRUCTOR table and we want to set the salary of each instructor to be 10000. Then the update statement will be.

Update INSTRUCTOR 
SET SALARY = 10000; 
ROW UPDATED 

On execution this will modify the salary of each instructor to 10000. Now suppose that we want to increase the salary of each INSTRUCTOR with a post ‘READER’ by 5000. The statement will be

Update INSTRUCTOR 
SET SALARY = SALARY + 5000; 
WHERE POST = 'READER'; 
Row Updated 

It is also possible to update multiple columns. Now suppose that you want to modify phone number to 2233710 and the post of the instructor, to ‘PROFESSOR’ with INST_ID = 102. To do this, we write the statement.

Update INSTRUCTOR 
Set Phone = 2233710 post = 'professor' 
Where inst_id = 102; 

To see the result after updations, use the following SELECT statement.

The following points should be remembered while executing UPDATE statement.

• It can only reference a single table.

• At least one column must be assigned an expression in the SET Clause.

• On omitting [Where condition], the UPDATE statement automatically updates all the records of the table on execution. So care should be taken when omitting where clause.

You can give multiple conditions in the where clause. For example:

Update instructor 
set salary = salary +500 
where post = 'Lecturer'and name = 'Anshu' 

• Any attempt to update a record with a value tied to integrity constraint generates an error.