Delete records in 2 tables with only 1 DELETE command

Asked

Viewed 77 times

3

Model of the structure of the tables:

Tabela: Produtos
id_produto
titulo_produto
descricao_produto

Tabela: Fotos
id_foto
descricao_foto
id_produto

We can observe that the tables have in common the id_product field. Based on this information we will assemble our script.

The command below simplifies the form of data deletion, rather than using separate Deletes.

DELETE FROM tabela1, tabela2, tabela3
USING tabela1
INNER JOIN tabela2 INNER JOIN tabela3
WHERE tabela1.id = tabela2.id
AND tabela2.id = tabela3.id
AND tabela1.id = '$id'"

Good so far everything ok, but how to solve this when we don’t have the id_product field in common?

Example:

Tabela: Produtos
id
titulo_produto
descricao_produto

Tabela: Fotos
id_foto
descricao_foto
id_produto
mysql mysqli

  • Interesting, I’ll follow... But what would be the need for this? Opening a transaction and commiting would also suit you or not?

  • 1

    Yes, but that’s a doubt I have for knowledge purposes only

  • See "Multiple-Table Syntax" in the manual https://dev.mysql.com/doc/refman/8.0/en/delete.html.

1 answer

2


Simple, just do the Join as in the example above:

DELETE Produtos, Fotos
FROM Produtos
INNER JOIN Fotos on Fotos.id_produto = Produtos.id
WHERE Produtos.id = '$id'"

  • I don’t understand, your SQL doesn’t work. because you used two FROM?

  • Okay, I fixed it. Just remove the first from.

  • Well I just found a problem, if there is no record in the table Fotos the product is not deleted. Do you know how to fix this? I tried to change the INNER JOIN for OUTER JOIN but it didn’t work out.

  • 2

    Instead of using INNER JOIN use LEFT JOIN.

  • in this callus it fails if there is a record in the table Fotos and if there is no table Produtos. It has to delete independent of one or the other. It has as?

  • Mysql does not support DELETE without the instruction FROM. In this case you should check first if the record exists in the products table and then delete from the two tables, if it does not exist, delete only from the pictures table.

Show 1 more comment

Browser other questions tagged mysql mysqli

You are not signed in. Login or sign up in order to post.