0

Here is my query:

$query="Delete b
Where Exists 
    (
        Select 1
        From a
        Where a.poster_password = '$pass'
        And a.ad_id = '$id'
        And a.classified_id = b.classified_id
    )
Delete a
Where a.poster_password = '$pass'
And a.ad_id = '$id'";

I get this error:

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 'Where Exists ( Select 1 From a Where a.poster_p' at line 2

If you need more input let me know...

Whats wrong here?

Thanks

UDPATE:

Just a Q: Do I need to specify also that a = "this table" and b = "another table" or does MySql get that by this code?

As for the new code posted where to use FROM and a terminator semicolon, wont work and give this error:

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 'Delete FROM a Where a.poster_password = 'xxxxxxxxxxxxxxxxxxxxx' at line 10

UPDATE2:

$query="Delete FROM $sql_table
        Where Exists 
        (
            Select 1
            From classified
            Where classified.poster_password = '$pass'
            And classified.ad_id = '$id'
            And classified.classified_id = $sql_table.classified_id
        );
        Delete FROM classified
        Where classified.poster_password = '$pass'
        And classified.ad_id = '$id'";

And when I echo $query: (fordon is in this case $sql_table variable.)

Delete FROM fordon 
Where Exists 
(  
     Select 1 
     From classified 
     Where classified.poster_password = 'xxxxx' 
     And classified.ad_id = 'motorbat_166250627' 
     And classified.classified_id = fordon.classified_id 
 ); 
 Delete FROM classified 
 Where classified.poster_password = 'xxxxx' 
 And classified.ad_id = 'motorbat_166250627'

Thanks again

Improve this question
2
  • Did you try to remove the second query (Delete a) and only execute the first one? Commented Jun 4, 2010 at 9:18
  • I think it was mysql version 5.1 btw... Peter: No I haven't Commented Jun 4, 2010 at 9:19

2 Answers 2

Reset to default
1

You're not specifying the tables to delete from. Try:

$query="Delete FROM b
Where Exists 
    (
        Select 1
        From a
        Where a.poster_password = '$pass'
        And a.ad_id = '$id'
        And a.classified_id = b.classified_id
    );
Delete FROM a
Where a.poster_password = '$pass'
And a.ad_id = '$id'";

I've also added in a semicolon after the end of the first DELETE query. If you want to run both at the same time, you'll need a separator to terminate the first query, before you run the second version.

Re. your question edit about MySQL "getting" the tables - if a and b are aliases here, then no, MySQL doesn't know what a and b are. You'll need to alias the tables, or replace a and b with the actual table names.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Check my update please, this wont work either for some reason
Okay, changed the aliases, but still get the same error in the update of my Q... hmm
Can you post your exact query string now?
0

The two deletes need to be separate statements ( and executed separately ).

Improve this answer

1 Comment

MySQL supposts multi-table deletes: dev.mysql.com/doc/refman/5.0/en/delete.html. However the OP's syntax appears incorrect. There should be a FROM in there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.