同时从2个表中删除

Question

I have two tables in DB

food

id  name  ... food_menu

and food_drinks

food_id    drinks_id

food_menu is equal to drinks_id. On the page I have button next to some food and when I click it is delete that food from table food. The problem is can I delete in the same time related drinks_id from food_drinks in same query?

This is what I use now to delete the food.

if ($stmt = $con->prepare("DELETE FROM food WHERE id = ? LIMIT 1"))
{
    $stmt->bind_param("i",$id);
    $stmt->execute();
    $stmt->close();
}

else
{
    echo "ERROR: could not prepare SQL statement.";
}
$con->close();

Answer 1

You can delete from multiple tables at once like this

DELETE f, fd
FROM food f
LEFT JOIN food_drinks fd on fd.drinks_id = f.food_menu
WHERE f.id = ?