How can a JOIN statement be used in a DELETE query in MySQL to efficiently delete records based on specific criteria?

When using a JOIN statement in a DELETE query in MySQL, we can efficiently delete records based on specific criteria by specifying the tables to join and the conditions for deletion in the WHERE clause. This allows us to target and delete records from multiple tables that meet the specified criteria in a single query. 

<?php
// Establish a MySQL database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Define the DELETE query with JOIN and WHERE clause
$sql = "DELETE t1, t2 FROM table1 t1
        JOIN table2 t2 ON t1.id = t2.id
        WHERE t1.column = 'value' AND t2.column = 'value'";

// Execute the DELETE query
if ($conn->query($sql) === TRUE) {
    echo "Records deleted successfully";
} else {
    echo "Error deleting records: " . $conn->error;
}

// Close the database connection
$conn->close();
?>

Keywords

JOIN DELETE query MySQL efficient specific criteria

Related Questions