You are here:   HomeStructured Query (SQL)SQL TutorialsSQL UPDATE
by Dinesh Thakur Category: SQL Tutorials

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.

The syntax is

UPDATE <tablename>

SET <columnnamel> = <expression> [, <column narne2>= <expression> ]

........... [,<columnnameN>=<expression> ]

[WHERE condition]; 



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.

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;

5 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’;

2 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.



Subscribe To Free Daily Newsletter!

Get Free News Updates Delivered Directly To Your Inbox
About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to contact us.



What's New and Popular





Search Content







Advance Courses



Basic Courses



Advertise with Us