Delete in 2 tables at the same time in a single query

Question

I have the following query:

DELETE FROM emails,emailsacompanhamento 
USING emails,emailsacompanhamento 
WHERE emails.idEmail = emailsacompanhamento.idEmail AND 
emails.idEmail = ".$idEmail

That makes a kind of JOIN in 2 tables and deletes in both at once.

Everything works well. But I need to create a parole that says:

if on the table entanglement there is no record whose field idEmail is equal to idEmail table Emails referenced in query, the query delete the table record Emails and disregard the table entanglement.

What would that be like query?

Answer 1

Use the DELETE with JOIN, in your case would look like this:

$sql = "
    DELETE emails, emailsacompanhamento FROM `emails`
    LEFT JOIN `emailsacompanhamento ` ON `emails`.`idEmail` = `emailsacompanhamento `.`idEmail`
    WHERE `emails`.`idEmail` = ".$idEmail;

Edit

In the SELECT you define which field will be returned before the FROM, example: SELECT campo1, campo2... FROM sua_tabela. In the case of DELETE you cannot delete a specific field, or delete all or delete nothing.

In a DELETE simple, you do not need to inform the table before the FROM, being like this:

DELETE FROM sua_tabela WHERE campo1 = value1;

But when the DELETE is composed of JOIN it is necessary to inform which tables will have the record removed if the condition returns True. These tables will be before the FROM as an example:

DELETE tabela1 FROM tabela1
INNER JOIN tabela2 ON tabela1.campo1 = tabela2.campo2

In the above example, only the table records tabela1 will be removed if the check tabela1.campo1 = tabela2.campo2 be it True.