Deletion of a record from a database table

You can delete a record from a table using the delete command.

Syntax

The following syntax shows how to use the delete command.

delete from  database_name.table_name
[ where conditional_test ] ;

Attention: Although the where condition is optional, omitting it deletes all the records in the table.

Basic example

The following example deletes all records from the staff.contractors table where Name="James".

|phoenix:1.> delete from staff.contractors
|phoenix:2.> where Name="James";
|phoenix:3.> go

Example of deleting part of an object or list

The following example removes records based on part of the contents of an object.

|phoenix:1.> delete from staff.contractors           // Delete records where
|phoenix:2.> where ExtraInfo->Department="Marketing";// the Department
|phoenix:3.> go                               // in ExtraInfo is Marketing.

The following example removes records based on part of the contents of a list.

|phoenix:1.> delete from staff.employees
|phoenix:2.> where Skills(0)="Perl";    // Delete records where "Perl"
|phoenix:3.> go                         // is the first list item.

The following example removes records based on the entire contents of a list.

|phoenix:1.> delete from staff.employees
|phoenix:2.> where Skills=["HTML", "C++", "Java"]; 
|phoenix:3.> go