MySQL syntax error for delete

[djbaxter]

What is wrong with this query?

DELETE FROM vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid) AND vbpmtext.dateline < $time

I am trying to run it from this PHP utility but it also gives me an error run from phpMyAdmin:


<?php

require ("global.php");
$time = time() - (90 * 24 * 60 * 60);

$query = "DELETE FROM vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid) AND vbpmtext.dateline < $time";
mysql_query($query);
print $query;
?>


This version seems to work:

DELETE FROM vbpmtext, vbpm, vbpmreceipt USING vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid) AND vbpmtext.dateline < $time

in the sense that it doesn't generate a syntax error. Anyone see anything wrong with that?

[nexialys]

You can't delete 3 table elements at the same time... you have to run three queries for that.

[harmor19]

You don't need to put the query into a variable then the variable into a method.
You can just use $db->query_write()
Example:
$db->query_write("DELETE FROM " . TABLE_PREFIX . "temp_host_request WHERE userid = '".$db->escape_string($vbulletin->GPC['postuserid'])."' ");


The syntax is "DELETE FROM [table]". You don't list the fields.

[Dismounted]

That depends if he's using a vBulletin backend or not.

[harmor19]

That depends if he's using a vBulletin backend or not.
He called "global.php" so I'm assuming he is.

[Marco van Herwaarden]

What is the MySQL version you are using and what is the exact error message you get?

[djbaxter]

Update: I'm not getting error messages using either of the following MySQL statements:

Version 1 (works):

$query = "DELETE FROM " . TABLE_PREFIX . "pmtext, " . TABLE_PREFIX . "pm, " . TABLE_PREFIX . "pmreceipt USING " . TABLE_PREFIX . "pmtext, " . TABLE_PREFIX . "pm, " . TABLE_PREFIX . "pmreceipt WHERE (" . TABLE_PREFIX . "pm.pmid = " . TABLE_PREFIX . "pmreceipt.pmid) AND (" . TABLE_PREFIX . "pm.pmtextid = " . TABLE_PREFIX . "pmtext.pmtextid) AND " . TABLE_PREFIX . "pmtext.dateline < $time";

Version 2 (works):

$query = "DELETE " . TABLE_PREFIX . "pmtext . * , " . TABLE_PREFIX . "pm . * , " . TABLE_PREFIX . "pmreceipt . * FROM " . TABLE_PREFIX . "pmtext, " . TABLE_PREFIX . "pm, " . TABLE_PREFIX . "pmreceipt WHERE " . TABLE_PREFIX . "pm.pmid = " . TABLE_PREFIX . "pmreceipt.pmid AND " . TABLE_PREFIX . "pm.pmtextid = " . TABLE_PREFIX . "pmtext.pmtextid AND " . TABLE_PREFIX . "pmtext.dateline < $time";

Is there any advantage of one over the other?

I will release this as an add-on when it's fully tested in case anyone else might find it useful.

I'm using PHP v4.4.4 with MySQL v4.1.21.

[Cap'n Steve]

This is an interesting topic, and I'll have to take a look at your version 1 query since I've never used USING. I've had a lot of trouble doing cross-table deletes recently, and so far I've found that listing the fields (table1.*, table2.*) and avoiding aliases seems to work the best.