how to delete data in oracle?

by john
(philippines)

what are the steps in deleting data in oracle?


There are 3 ways of deleting data in Oracle that a developer is likely to use (and probably another 3 that a dba would use, but we'll just concentrate on the developer's methods). You can delete the data in one or more columns, in one or more rows, or in the whole table in one go.

To delete column data you need to use the UPDATE statement to set the columns to NULL as in the following example.

UPDATE employee SET salary=NULL WHERE last_name = 'Jones';

That will delete the salary information for all employees called Jones. The WHERE clause is an optional filter on the table and restricts the number of rows affected. Without the WHERE clause all rows are updated.

To delete one or more rows in the table you need to use the DELETE statement as in the following example.

DELETE employee WHERE last_name = 'Jones';

The above statement will remove from the table all rows for employees called Jones. Again, the where clause is optional. Without the where clause all rows in the table will be deleted. It is not generally advisable to delete everything in a table this way as it is not an efficient way of doing so. The next example shows a more efficient way of deleting (the contents of) a whole table.

TRUNCATE TABLE employee ;

The TRUNCATE statement deletes all the rows in the table in one go. It is more efficient because it cannot be undone as no information about the original state of each row (undo information) is logged. By default, the TRUNCATE statement also deallocates the space used by the table.

For more help with SQL see our Oracle training page for details of instructor-led training in the UK and US as well as on-line, on-demand training.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.