SQL DELETE Statement Tutorial

SQL DELETE Statement Tutorial

by Ignis Dei
15 views

In this article, We’ll go through how you can use the SQL DELETE statement. In order not to have to install any RDBMS to go through this example, we’ll use Postgres online from extendsclass.com.

1. Why do we need SQL DELETE statement?

In SQL, the DELETE statement removes one or more records from a table. A set within a larger set can be defined for deletion using a condition with the help of WHERE keyword or else all records are removed. Some RDBMSs such as MySQL, allow deletion of rows from multiple tables with one DELETE statement.

2. SQL DELETE Statement Syntax

The syntax of the SQL DELETE Statement is:

DELETE FROM table
WHERE [condition];

The condition can be more than one and we can combine them with the help of AND and OR operators.

3. SQL DELETE statement examples

In this section, we will show you some examples of SQL DELETE statements but first of all, we need to create a table as an example with the name Erasmus:

CREATE TABLE erasmus (
	id integer,
	name varchar(100),
	country varchar(100),
	age integer
);

INSERT INTO erasmus
 	(id, name, country,age)
VALUES
    (1, 'John','USA',25),
    (2, 'Georgios','Greece',25),
    (3, 'Jim','Spain',28),
    (4, 'Odysseas','United Kingdom',30),
    (5, 'Helen','Belgium',32),
    (6, 'Constantine','Poland',34),
    (7, 'Sia','Greece',25),
    (8, 'Nikolaos','USA',28),
    (9, 'Lucas','Russia',29),
    (10,'Philip','Serbia',35);

		
SELECT * FROM erasmus;

The output is:

idnamecountryage
1JohnUSA25
2GeorgiosGreece25
3JimSpain28
4OdysseasUnited Kingdom30
5HelenBelgium32
6ConstantinePoland34
7SiaGreece25
8NikolaosUSA28
9LucasRussia29
10PhilipSerbia35
Table 1 – Erasmus table

3.1 Delete all rows from a table

With the DELETE statement is possible to delete all rows in a table without deleting the table and its structure, indexes, and attributes. Here is an example:

DELETE from erasmus;
select * from erasmus;

The output is:

idnamecountryage
Table 2 – DELETE all rows

3.2 Delete existing records

Furthermore with the DELETE statement is possible to delete existing records in a table with the assistance of the WHERE operator. Here is an example:

DELETE FROM erasmus WHERE country='Greece';
SELECT* FROM erasmus;

The output is:

idnamecountryage
1JohnUSA25
3JimSpain28
4OdysseasUnited Kingdom30
5HelenBelgium32
6ConstantinePoland34
8NikolaosUSA28
9LucasRussia29
10PhilipSerbia35
Table 3 – Delete existing records

4. Conclusion

By now you should be able to know when to use SQL DELETE statement and how to use it in the most efficient way. You can find the source code on our Github Page.

Related Posts