Delete Data from Table

Delete data from database is one crucial task for database administrator. Unlike removing a table or a database, delete data from table uses the DELETE statements.

The syntax to delete data from table is:

Figure 1 below shows the deletion of data from table "students":

Figure 1: Delete the data for "Ahmad Samsul" from table "students" using the student ID.

It is crucial for the database administrator to make sure the WHERE clause is stated in the SQL command when data from a table. This is because when user executing the SQL command without the WHERE clause, the database will act to delete all the data in the selected table. The Figure 2 below shows the results when the WHERE clause is missing when executing the SQL statements.

Figure 2: Executing DELETE statements without WHERE clause.

Selecting the correct condition is also important in performing delete operation in database. Usually the deletion of a particular data from table is based on primary key, this is because the primary key is the key reference point for a data.

However, sometimes user can delete a set of data from the table by using certain column in WHERE clause. Figure 3 shows how user to delete a set of data that share the same data (same intake year) from the table "students".

Figure 3: Delete a set of data based on the same "intakeyear" column.

* Lab demo, please show the class how to insert the extra data into the table, or re-enter the data into the table to demo the different DELETE statements. Use Insert Page as reference.