Deletion

1. Deletion is expressed in much the same way as a query. Instead of displaying, the selected tuples are removed from the database. We can only delete whole tuples.

2. A deletion in SQL is of the form

   

   Tuples in for which is true are deleted. If the where clause is omitted, all tuples are deleted.

3. The request delete borrow deletes all tuples from the relation borrow.

4. Some more examples:
   1. Delete all of Smith's account records.

      

   2. Delete all loans with loan numbers between 1300 and 1500.

      

   3. Delete all accounts at branches located in Surrey.

      

5. We may only delete tuples from one relation at a time, but we may reference any number of relations in a select-from-where clause embedded in the where clause of a delete.

6. However, if the delete request contains an embedded select that references the relation from which tuples are to be deleted, ambiguities may result.

   For example, to delete the records of all accounts with balances below the average, we might write

   

   You can see that as we delete tuples from deposit, the average balance changes!

   Solution: The SQL standard does not allow delete requests like this. Another possible solution would be to only mark the tuples to be deleted, then delete them en masse after evaluations.