SQL – Delete Command

Situation may rise where we may want to delete data from a table. To tackle such situation, SQL is providing Delete command. By using SQL delete command we can instruct mysql to delete a record.

SQL Delete Command – DELETING SINGLE ROW FROM TABLE

SQL delete command Statement Syntax:

DELETE FROM TABLE_NAME WHERE SOME_COLUMN=SOME_VALUE

EXAMPLE:

Roll_no Student_name Subject Marks
1 John Math 90
2 Rita Math 95
3 Sam Math 92
4 Jack Math 84
5 Pom Math 99

Lets take consideration of same table we have referring so far.

Suppose a requirement came to delete the record of roll_no 2.

The syntax would be as below:

delete from new_student_marks where roll_no=2

Note: new_student_marks is the table we have created in the phpmyadmin mysql page in one of the previous chapter.

Now, launch xampp, select the database, then click on SQL from menu panel.

SQL Command box will appear.

Inside the command box, paste above command and run it.

Delete row from a table using sql command Delete row from a table using sql command Delete row from a table using sql command

The moment you click on go, it will show a message for confirmation. Click on ok. Status message will appear, which says ‘1 row affected’.

If we check our table, then we can notice, the required row is deleted.

Delete row from a table using sql command

See in the table, roll_no 2 record is deleted.

In similar way we can also delete by referring to student name.

Suppose a requirement came to delete the record whose name is jack.

The syntax would be as below:

delete from new_student_marks where student_name=’jack’

Note: if you see, we have kept the name jack in between single quote. Because string value (varchar) is provided with in single quotes in the command. (We have discussed this in one of the previous tutorial)

Now run the command in the SQL Command box and you will notice, the row is deleted.

WARNING:

Be careful while deleting rows from a table. If you mistakenly refer to any other value, then it would delete that record. 

SQL DELETE COMMAND – DELETING MULTIPLE ROWS FROM A TABLE 

In sql, we can also delete multiple records by passing required column values. (For understanding purpose, follow but avoid if you are absolutely new learner. Once you understand the basics then you learn such advanced concepts which we are going to discuss in upcoming chapters)

Suppose I want to delete roll no 3,4 and 5 record from the table. Then the syntax will be as below:

delete from new_student_marks where roll_no in (3,4,5)

Run the command in SQL Command box.

delete multiple rows with sql command delete multiple rows with sql command delete multiple rows with sql command delete multiple rows with sql command

Once we run the command, it will ask for confirmation. Upon confirmation, it will delete the mentioned records from the table. See our table has deleted roll_no 3, roll_no 4 and roll_no 5 record.

NOTE: ‘in’  is reserved keyword for sql command and it checks whether a value is present inside a set of value. In our case, we have provided roll no 3,4, 5. So the command will run the delete task for each row, comparing whether the roll no of a row matches with the one of the roll no value provided in the sql command. If match found, then it deletes other wise it ignores and proceeds to next row. This cycle continues for all the rows.

And finally, three rows only deleted.