SQL delete Command
Delete query is used to delete records from the table. This query is used along with some condition to selectively delete records from the table. To delete all the records from the table we can use truncate command like this
This will remove all the records from the table. This is one way of deleting all the records from a table. Now let us try to delete records based on some selection. Now let us delete records of the students who has got mark less than 60. Here we are selectively deleting the records so we have to use WHERE clause to match the selection.
DELETE FROM student WHERE mark < 60
This will delete records for which mark is less than 60.
More on SQL WHERE command tutorial .
Deleting records from multiple tables.
We can delete linked records from two tables by using single query. We know that there are two records in student_fee table and one record in student table for student id =2. We will delete them in single query
DELETE student,student_fee FROM student inner join student_fee on student.id=student_fee.id and student.id=2
Above query will remove two records from student_fee table and one from student table
Now let us remove all the records from student table where they are present in student_fee table. This is same as deleting all records which are present in both tables.
DELETE student,student_fee FROM student inner join student_fee on student.id=student_fee.id
Deleting records that don't exist in another table
This is required when we want to delete students who have not paid their fees. Now we will remove such records from first table ( student ) for which matching record is not available in second table ( student_fee ).
By using left Join
DELETE student FROM student LEFT JOIN student_fee on student_fee.id=student.id WHERE student_fee.id IS NULL
By using NOT EXISTS and subquery
DELETE FROM student WHERE NOT EXISTS ( SELECT * FROM student_fee where student_fee.id=student.id)
By using NOT IN
DELETE FROM student WHERE student.id NOT IN ( SELECT id FROM student_fee where student_fee.id=student.id)
How to delete tabel or database ?
Download sql dump of this student table
Download sql dump of linked student_fee table
Be the first to post comment on this article :
|More on Alter or coy table , add update or delete records in SQL|