CODEWITHSUNDEEP
phpmysql
Mashhood Ali
Mashhood Ali

Reputation: 127

my delete query isn't working

i am trying to delete from multiple tables in one query in mysql. it is giving me the following error:

ERROR:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM posts INNER JOIN fields_data ON posts.post_id = fields_data.post_id W' at line 1

<?php
include "connection.php";

$id=@$_GET['pid'];

$ssq="DELETE FROM `posts` INNER JOIN `fields_data` ON posts.post_id =   
fields_data.post_id WHERE posts.post_id= '$id'";
$query=mysqli_query($con,$ssq);
if($query){
 header("location:home.php?del=The post has been deleted successfully");
 }
else{
echo "ERROR:".mysqli_error($con);
}

?>

Upvotes: 0

Views: 76

Answers (2)

Raging Bull
Raging Bull

Reputation: 18737

Use the table name after DELETE keyword:

$ssq="DELETE `posts` FROM `posts` INNER JOIN `fields_data` ON posts.post_id =   
fields_data.post_id WHERE posts.post_id= '$id'";

Using alias names for better understanding

$ssq="DELETE p FROM `posts` as p INNER JOIN `fields_data` as fd ON p.post_id =   
fd.post_id WHERE p.post_id= '$id'";

EDIT:

If you want to delete the records from both tables, just specify the table names after DELETE keyword:

$ssq="DELETE `posts`,`fields_data` FROM `posts` INNER JOIN `fields_data` ON posts.post_id =   
fields_data.post_id WHERE posts.post_id= '$id'";

Upvotes: 2

Deenadhayalan Manoharan
Deenadhayalan Manoharan

Reputation: 5444

Try this..

You often use the INNER JOIN clause in the SELECT statement to select records from a table that have corresponding records in other tables. To make it more convenient, MySQL also allows you to use the INNER JOIN clause with the DELETE statement to delete records from a table and also the corresponding records in other tables e.g., to delete records from both T1 and T2 tables that meet a particular condition, you use the following statement:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition

Your code 

$ssq="DELETE posts,fields_data FROM `posts` as posts INNER JOIN `fields_data`as fields_data ON posts.post_id =   
fields_data.post_id WHERE posts.post_id= '$id'";

http://www.mysqltutorial.org/mysql-delete-join/

Upvotes: 2

Related Questions