Delete function

[xxreenaxx1]

I have two delete function and would like to make this as one. So I can use this on my webpage. But not sure how? Anyone willing to help me :D

DELETE
       FROM test
       WHERE 
       Tes_ID=$Tes_ID;

     DELETE
       FROM question
       WHERE 
       Tes_ID=$Tes_ID

[jkon]

Deferent tables deferent queries... Why that bothers you?

[paulrajj]

I think Tes_ID is common for both tables. If so, try with the below query.

DELETE FROM test as t1
 WHERE t1.Tes_ID = '$Tes_ID' 
   AND t1.Tes_ID IN (SELECT t2.Tes_ID 
                     FROM question as t2
                    WHERE t2.Tes_ID = '$Tes_ID')

[diafol]

You may want to look at the ROLLUP function in mysql. You need foreign keys I think.

[xxreenaxx1]

@jkon: because I am trying to delete both table using PHP and it doesn excute both of the query.

@paulraj.. I am getting an error message, which I been getting for all the other methods I have tired.

#1064 - 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 'as t1 WHERE t1.Tes_ID = 12 AND t1.Tes_ID IN (SELECT t2.Tes' at line 1

@ardav... I do have foreign key. These two tables are actually linked by Tes_ID.

[diafol]

I think they need to be constrained though. ROLLUP should automatically delete any related records.

[paulrajj]

@xxreenaxx1, I edited my post by using joins to delete the records. Here is below,

DELETE t1, t2 FROM 
	`question` AS t1 
	LEFT JOIN `test` AS t2 
	ON t1.Tes_ID = t2.Tes_ID 
	WHERE t1.Tes_ID = '1'

[xxreenaxx1]

@ardav.. Thank you for the help

@paulraj... it worked. Thank you :)