PDA

View Full Version : MySQL syntax error for delete

djbaxter
14 Mar 2007, 18:05
What is wrong with this query?



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



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



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



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
14 Mar 2007, 19:15
You can't delete 3 table elements at the same time... you have to run three queries for that.
harmor19
14 Mar 2007, 23:26
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:


Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------




The syntax is "DELETE FROM [table]". You don't list the fields.
Dismounted
15 Mar 2007, 06:39
That depends if he's using a vBulletin backend or not.
harmor19
15 Mar 2007, 07:12
That depends if he's using a vBulletin backend or not.
He called "global.php" so I'm assuming he is.
Marco van Herwaarden
15 Mar 2007, 08:45
What is the MySQL version you are using and what is the exact error message you get?
djbaxter
15 Mar 2007, 14:31
Update: I'm not getting error messages using either of the following MySQL statements:

Version 1 (works):



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



Version 2 (works):



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



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
17 Mar 2007, 10:34
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.