Slide 11.14: The DELETE and DROP statements

Slide 11.13: The UPDATE statement
Slide 11.15: The ORDER BY keyword
Home

The DELETE and DROP Statements

The DELETE statement is used to delete rows in a table. The syntax of a delete statement is:

  DELETE FROM  table_name
    WHERE  column_name = some_value

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger
Rasmussen	Nina	Stien 12	Stavanger

 SQL> delete from  Persons  where  LastName = 'Rasmussen';

 1 row deleted.

 SQL> delete from  Persons;

 3 rows deleted.

The first delete statement deletes “Nina Rasmussen.”

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger

The second delete statement deletes all rows in the Persons table without deleting the table. This means that the table structure, attributes, and indexes will be intact.

LastName	FirstName	Address	City

The DROP command is to delete a table (the table structure, attributes, and indexes will also be deleted):

 DROP  TABLE  table_name

The following is an SQL test area from w3schools.com where the Customers table may be created by the following command:

   SQL> create table  Customers (
     2    CustomerID   varchar(16),
     3    CompanyName  varchar(32), 
     4    ContactName  varchar(32),
     5    Address      varchar(64),
     6    City         varchar(32), 
     7    PostalCode   varchar(16), 
     8    Country      varchar(32) );

Note that the Customers table is for read only.

	SQL Execution Results
select * from Customers where Country like '%S%';