SQL delete Command

SQL to delete records using WHER condition and removing records from multiple tables by joining

DELETE and TRUNCATE

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.
TRUNCATE `student`
This will remove all the records from the table. This is one way of deleting all the records from a table. We can 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 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)

Difference Between DROP, TRUNCATE, and DELETE

  • DROP: Removes an entire table structure along with its data irreversibly (in most cases).
  • TRUNCATE: Quickly removes all rows from a table, more efficient than DELETE for clearing a table, but less flexible. Blank table with structure remains.
  • DELETE: Deletes rows specified by a WHERE clause, allowing for selective removal of data, but is slower for large datasets due to logging.

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 linked student_fee table

SQL References How to delete table or database
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer