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

Question

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

Navigation

#1 by (3 votes)

2

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 tables and delete in both in one go.

Everything works fine. But I need to create a conditional that says:

If there is no record whose idEmail field is equal to idEmail of the Emails table referenced in emails table > query , the query deletes the record of the Emails table and disregards the emails table.

How would this query be?

php mysql

asked by anonymous 25.05.2018 / 21:16

1 answer

Make a DIV element be positioned as a footer How to upload videos inside a div with select

score 3 · Accepted Answer

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

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

Edit

No SELECT you define which fields are returned before FROM , example: SELECT campo1, campo2... FROM sua_tabela . In the case of DELETE you can not delete a specific field, either delete all or delete nothing.

In% simple%, you do not need to inform the table before DELETE , thus:

DELETE FROM sua_tabela WHERE campo1 = value1;

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

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

In the example above, only the records of the FROM table will be removed if the tabela1 check is tabela1.campo1 = tabela2.campo2 .