SQL delete Command

TRUNCATE `student`

DELETE FROM student WHERE mark < 60 

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 from three tables

Here are three tables linked to each other ( Country > State > City )

DELETE  FROM plus2_country,plus2_state,plus2_city USING plus2_state INNER JOIN plus2_city INNER JOIN plus2_country WHERE plus2_country.country_code=plus2_state.country_code AND plus2_state.state_id=plus2_city.state_id AND  plus2_country.country_code='CAN'

This single query will delete CAN from plus2_country , delete all records from table plus2_state having country code ='CAN' and Delete all records from plus2_city for matching STATE of Country Canada

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)

Executing delete query using PHP Script

After preparing the SQL we can execute the query from our PHP Script. Here is sample code using Php PDO.

config.php file is the database connection file.
$query is the string variable storing our SQL

require "config.php";// Database connection

$count=$dbo->prepare($query);

$count->execute();

$no=$count->rowCount();

echo " No of records deleted = ".$no;

Download sql dump of this student table

Download sql dump of linked student_fee table



Tweet