| Team Fly | |
Use the delete statement when you want to remove one or more rows of data from a table. The command has two parts:
1. The keywords delete from followed by the table name you want to remove records from. This is mandatory.
2. A where clause followed by the record selection criteria. This is optional. As with the update, absence of a where clause will remove every record from the table.
If you want to remove all the customers from the CUSTOMERS table, you would issue the SQL statement delete from customer;. As you become more familiar with Oracle, you will learn that the trunc command will also remove every record but this doesn't allow you to rollback the changes if you make a mistake. It's very easy to accidentally drop all the records in a table. As with the update statement, be very careful when issuing the delete or truncate commands.
Let us now illustrate a deletion of all customers in the province of Delhi. The code listing will first show a count of customers in Delhi, introducing count(*) for the first time. This is being used to illustrate the number of records we expect to delete when we issue the command. The second SQL statement issues the delete from command, which confirms the number of records deleted. The final SQL statement is a repeat of the first one to illustrate that there are no records remaining for the province of Delhi. In order to continue to use these records for later examples, I will rollback the changes so they never get permanently committed to the database and re-run the first SQL statement one more time to confirm that the records have been restored.
SQL> select count(*)
2 from customers
3 where cust_state_province = 'Delhi';
COUNT(*)
----------
34
SQL> delete from customers
2 where cust_state_province = 'Delhi';
34 rows deleted.
SQL> select count(*)
2 from customers
3 where cust_state_province = 'Delhi';
COUNT(*)
| Team Fly | |