Delete one row in SQL

Here, you will learn how to delete one row or record from a table in Structured Query Language.

We can easily delete one record using the SQL DELETE statement. This statement also removes all the existing rows from the database tables. It also helps in removing the data from the SQL views.

Once a row has been deleted from the table, that row cannot be recovered.

The SQL syntax for deleting a specific row is given below:

DELETE FROM Table_Name WHERE condition;

In this syntax, the WHERE clause specifies that record which you want to remove from the table. If you run the DELETE command without WHERE clause, the query will remove all the rows from the SQL table.

If you want to remove the records permanently from the table, you have to follow the following steps one by one in the given order:

Create a Database.
Create a Table and Insert the data into the table.
Show the table before deletion.
Delete one record from the table.
Show the table after deletion.

Now, we are going to explain the above steps with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL command to create a database:

CREATE DATABASE Database_Name;

Suppose you want to create a College database. For this operation, you have to type the following command in Structured Query Language:

CREATE DATABASE College;

Step 2: Create a Table and Insert the data

After database creation, you can create the table in the database with the help of the following syntax:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_N)
);

Suppose you want to create the Student table with five columns in the College database. For this, you have to write the following query:

CREATE TABLE Student
(
Roll_No Int,
First_Name Varchar (20),
City Varchar (20),
Age Int,
Marks Int,
) ;

Now, you have to insert the data in the table using the following syntax:

INSERT INTO <Table_Name> VALUES (value_1, value_2, value_3, ...., value_N);

You can easily insert the data of college students in the Student table using the following query in SQL:

INSERT INTO Student VALUES (101, Akash, Delhi, 18, 89),
(102, Bhavesh, Kanpur, 19, 93),
(103, Yash, Delhi, 20, 89),
(104, Bhavna, Delhi, 19, 78),
(105, Yatin, Lucknow, 20, 75),
(106, Ishika, Ghaziabad, 19, 91),
(107, Vivek, Goa, 20, 80);

Step 3: View the Inserted Data

After table creation and data insertion, you can view the inserted data of the Student table by typing the following query in your SQL application:

SELECT * FROM Student;

Roll_No	Name	City	Age	Marks
101	Akash	Delhi	18	89
102	Bhavesh	Kanpur	19	93
103	Yash	Delhi	20	89
104	Bhavna	Delhi	19	78
105	Yatin	Lucknow	20	75
106	Ishika	Ghaziabad	19	91
107	Vivek	Goa	20	80

Step 4: Delete One Record from the table

The following query deletes the record of the specific student from the above student table:

DELETE FROM Student WHERE Roll_No = 102;

Step 5: View the Table after Deletion

To check the result of the query executed in the 4th step, you have to use the following SELECT command in SQL:

SELECT * FROM Student;

Roll_No	Name	City	Age	Marks
101	Akash	Delhi	18	89
103	Yash	Delhi	20	89
104	Bhavna	Delhi	19	78
105	Yatin	Lucknow	20	75
106	Ishika	Ghaziabad	19	91
107	Vivek	Goa	20	80

As you can see that one row has been successfully deleted from the Student table based on the situation specified in the WHERE clause of the DELETE query.

Delete Multiple Records from the table

If you want to delete the multiple rows from the Students table, you have to write the below query in your SQL application:

DELETE FROM Student WHERE Roll_No >= 105;

To check the result of the above query, you have to write the following SELECT query in SQL:

SELECT * FROM Student;

Roll_No	Name	City	Age	Marks
101	Akash	Delhi	18	89
103	Yash	Delhi	20	89
104	Bhavna	Delhi	19	78

As we can see that more than one row has been deleted from the Student table according to the condition specified in the WHERE clause of the DELETE statement.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz