0

I am trying to do a simple delete from two tables, but the field names and values are the same for each query, so I am trying to combine the two into one query. Is it possible?

$results = $mysqli->query("DELETE FROM `recent_keys`, `recent_queries` WHERE `expires` < '$now_ts'"); // not working
MultiDev
  • 10,389
  • 24
  • 81
  • 148
  • 1
    possible duplicate of [delete from two tables in one query](http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query) – Clément Malet Aug 04 '14 at 14:05

2 Answers2

0

you can't manipulate 2 different tables in one query. try executing 2 queries at once by seperating them with semicolon:

$results = $mysqli->multi_query("DELETE FROM `recent_keys` WHERE `expires` < '$now_ts';  DELETE FROM `recent_queries` WHERE `expires` < '$now_ts';");
Volkan Ulukut
  • 4,230
  • 1
  • 20
  • 38
  • Thank you! And I'm assuming doing it like this in one query would be faster than doing them both separately? (actually this query is not working...) – MultiDev Aug 04 '14 at 14:08
  • indeed. you only send the query to database once, hence increasing performance. – Volkan Ulukut Aug 04 '14 at 14:14
  • @VolkanUlukut this does not work with `mysqli->query`->`Performs a query against the database.`. You would have to use [`mysqi->multi_query()`](http://php.net/manual/en/mysqli.multi-query.php)->`Executes one or multiple queries which are concatenated by a semicolon` – Sean Aug 04 '14 at 14:18
0

This worked for me:

$mysqli->query("DELETE k.*, q.* FROM recent_keys k JOIN recent_queries q ON k.expires = q.expires WHERE k.expires < '$now_ts'");
Javelin
  • 61
  • 4