Running two DELETE statements causes a syntax error

Question

    DELETE `member`
    FROM `member`
    WHERE `member`.`type_id` = 1;

    DELETE `subscriptions`
    FROM `subscriptions`
    WHERE `subscriptions`.`type_id` =  1;   

Table: subscriptions Columns: id int(11) PK AI type_id int(11)

Table: member Columns: id int(11) PK AI type_id int(11)

With the above I seem to be getting the following:

 Mysql2::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 `subscriptions`
        FROM `subscriptions`
        WHERE `subscriptions' at line 5: 

I think the problem that I am having is that I am trying to run both queries at the same time. I have also put this through a syntax checker and it gives the following: screenshot.

On a side note notice that both tables contain the attribute type_id. I did try to condense this by using an inner join:

DELETE FROM member, subscriptions
USING member
INNER JOIN subscriptions ON type.id = type_id
WHERE type.id= 1; 

But I believe the above inner join isn't correct

Answer 1

You can't* combine multiple queries like this. You need to execute them separately.

When you see the error ...the right syntax to use near... that's telling you that MySQL's parser (lexer?) can't come up with any valid reason why it found whatever it's complaining about in the specific place within the statement where it was encountered... in this case, the beginning of a second query.

You'll need to send them to the server separately.

If these two deletions need to occur together, atomically, that should be handled in a transaction.

---

*"can't" -- actually, with some libraries, you can enable multi-statement execution, but don't do that. It opens up a whole new world of sql injection vulnerabilities.

Answer 2

This technique is possible. Here is the correct syntax:

DELETE A.*,B.* FROM
member A INNER JOIN subscriptions B USING (type_id)
WHERE A.type_id = 1;

I have another post from June 26, 2013 on this same DELETE syntax on multiple tables : Deleting Data From Multiple Tables

As for the original question, @Michael-sqlbot has it right. The particular tool you are using does not support executing multiple queries. At the very least, it is not configured to run multiple queries.