MySQL Delete Records from 2 Tables

Question

Hey everyone,

Im looking to delete information in two different tables in 1 query, based on an ID.

I've tried several solutions on here to accomplish this task but still have not accomplished what I'm trying to do.

Table 1 - Content

---------- ---------
 ContentID | Content
--------------------

Table 2 - Votes

---------------------------
 VoteID | ContentID | Vote 
---------------------------

I want to delete the content row based on its ID and any or all votes (there could be 0 vote records). I do NOT want to use transactions, cascading deletes, or use 2 different queries.

What is best here - a LEFT JOIN? INNER JOIN?

Any help here would be greatly appreciated, thank you

Answer 1

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Although this is for 3 tables, I'm sure you'll be able to adapt it to your needs.

Answer 2

If you have a relation you can try with the ON DELETE CASCADE option.

Another option is to create a stored procedure and do the delete in 2 steps but with only one server call.

Answer 3

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.7.1, “JOIN Syntax”.

The first multiple-table DELETE syntax is supported starting from MySQL 4.0.0. The second is supported starting from MySQL 4.0.2.

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

This link can be useful "http://dev.mysql.com/doc/refman/4.1/en/delete.html"

Answer 4

DELETE Content, Votes
FROM Content
LEFT JOIN Votes
ON Votes.ContentID = Content.ContentID
WHERE Content.ContentID = ?

Answer 5

Hi , Try This

DELETE FROM AccountsToUser

FROM AccountsToUser AS AU INNER JOIN

Accounts as A ON a.AccountId = au.AccountId

WHERE a.OwnedByAccountId in (select AccountId from Accounts where OwnedByAccountId is null)