How to work with SQL DELETE Statement

In this tutorial we will demonstrate the use of delete statement. The DELETE statement is used to delete records in a table. General delete syntax

Sql;DELETE FROM table_name WHERE some_column=some value;

Before we do this create the table and add data to it using the following sql sintax: Script:

--first lets drop the table Test (if she exists, if not skip the drop line).Drop table test;
create table test (id int,name varchar(20),email varchar(20));
insert into test values (1,'Eve','[email protected]');
insert into test values (2,'Jon','[email protected]');
insert into test values (3,'Mike','[email protected]');
insert into test values (4,'Paul','[email protected]');

This will be the initial table

SQLselect * from test;
ID NAME         EMAIL
---------- -------------------- --------------------
1 Eve         [email protected]
2 Jon         [email protected]
3 Mike         [email protected]
4 Paul         [email protected]

Very well. Now let's start using delete statement on our Test table : Example:

SQLdelete from test where id=1;

Check the table data now

SQL
select * from test ;
ID NAME         EMAIL
---------- -------------------- --------------------
2 Jon         [email protected]
3 Mike         [email protected]
4 Paul         [email protected]

We can see that the row that had the ID=1 is no longer in our table We can also use any other column with our "where" statement. Delete all rows from a table

Sql DELETE FROM table_name;

Example:

Sql delete from test;

All records will be deleted. Check the table

Sql select * from test;

No lines will show up .

Sql DELETE * FROM table_name;

Will produce the same result as above. Very Important: You cannot recover the data after a "delete from *" statement. If the delete command was committed all the records in the table will be deleted. We will talk about commit in future tutorials. In some databases this is possible using (flashback, backups, etc), but this is an advanced topic which we will discuss in future tutorials. For now remember to always use where clause with you delete statements unless you want all the data to be deleted. There are various types of conditions that we can combine in our delete statements which we will abort in future tutorials.