Can i delete an entry on multiple tables in one SQL query?

I have 5 tables in my database : Category1 , Category2 , Category3 , Category4 , Category5

I have a user that might or might not be found on these tables. If he is found , i want to DELETE him from the table.

I could do that with 5 queries like this :

//search if the user is already subscribed in every one of the categories 
$stmt = $db->prepare("Select * FROM category1 WHERE deviceUDID = :deviceUDID"); 
$stmt->execute(array(':deviceUDID' => $deviceUDID));

if($rows_found = $stmt ->rowCount()) {    
  $stmt = $db -> prepare("DELETE FROM category1 WHERE deviceUDID =:deviceUDID");    
  $stmt->execute(array(':deviceUDID' => $deviceUDID));
}

Then for category2 :

//search if the user is already subscribed in every one of the categories 
$stmt = $db->prepare("Select * FROM category2 WHERE deviceUDID = :deviceUDID"); 
$stmt->execute(array(':deviceUDID' => $deviceUDID));

if($rows_found = $stmt ->rowCount()) {    
  $stmt = $db -> prepare("DELETE FROM category1 WHERE deviceUDID =:deviceUDID");    
  $stmt->execute(array(':deviceUDID' => $deviceUDID));
}

and so on for the rest of the categories..

Would it be possible to search in all these categories with 1 query to make it faster?

But i cant see the logic how something like that could happen.. Any ideas?

EDIT (BONUS PART)

Would be easier ,faster or more efficient to just make a DELETE query every time , even if the user is not there and let the query fail?

Or i should first check every table and if found make a DELETE query?

Which one could be more effective?

Answers


DELETE clause allows many tables in one request. try out :

DELETE FROM category1 c1, category2 c2, categorie3 c3 WHERE c1.deviceUDID= :deviceUDID AND c2.deviceUDID= :deviceUDID AND c3.deviceUDID= :deviceUDID ...

If you are worried about atomicity, enclosing the 5 deletes in a transaction covers that.

Alternatively, mysql should by now support foreign keys and cascaded deletes, so if you set up the foreign key relationship with your "user" table, you may have nothing more to do.


No. You could create a view that would tell you which categories a device was present in, but it would not be updateable.

create view allCategories as
 select 1 as category, *
 from   category1
 union
 select 2 as category, *
 from   category2
 union
 select 3 as category, *
 from   category3
 union
 select 4 as category, *
 from   category4
 union
 select 5 as category, *
 from   category5

Need Your Help

Mac unix script: given a list of file paths, find the one with latest modified date?

osx bash bsd

If I have a list of file names (absolute path), how do I determine which one is last modified in command line?

How to pass a variable from View to Controller in ASP .NET

javascript html asp.net asp.net-mvc

I found similar questions to mine, but in all of those examples, the variable was part of the model. I am trying to pass a variable that is created in javascript, which is not part of the model.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.