DELETE

The DELETE operator is used to erase records (not table structure).  The number of records deleted may be 0, 1, or many, depending on how many satisfy the predicate.

Format:
DELETE FROM table/view WHERE delete_criteria;

Example 1

Erase the record of student ‘S1020′  (Delete a single tuple)
DELETE FROM STUDENT WHERE STUID = ‘S1020′;

Example 2

Erase all enrollment records for student ‘S1020′.  (Delete several tuples).
DELETE FROM ENROLL WHERE STUID = ‘S1020′;

Example 3

Erase all the class records.  (Deleting all the tuples from a table)
DELETE FROM CLASS;

Note that the table CLASS still exists, but is empty.  To remove the data and the table you use the DROP TABLE operator.

Example 4

Erase all enrollment records for Owen McCarthy.  (Delete with a subquery)
DELETE FROM ENROLL WHERE STUID = (SELECT STUID FROM STUDENT WHERE FNAME = ‘Owen’ AND LNAME = ‘McCarthy’);

In our database there is no such student, so no records are deleted from ENROLL.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>