0

This image show the database diagram

SET  @ID = (SELECT ID FROM pessoa WHERE CPF=123456);
SET  @ID = (SELECT ENDERECO FROM pessoa WHERE CPF=123456);
SET @TELEFONES = (SELECT ID_TELEFONE FROM pessoa_telefone WHERE ID_PESSOA=@ID);
SET @TIPOS = (SELECT TIPO FROM telefone WHERE ID IN (@TELEFONES));

DELETE FROM pessoa_telefone WHERE ID_PESSOA=@ID;

DELETE FROM pessoa WHERE ID=@ID;
DELETE FROM endereco WHERE ID=@ENDERECO;

DELETE FROM telefone WHERE ID IN (@TELEFONES);
DELETE FROM telefone_tipo WHERE ID IN (@TIPOS);

This code works, but how reduce to something like this:

DELETE FROM pessoa_telefone, pessoa, endereco, telefone, telefone_tipo
JOIN pessoa, JOIN endereco,
JOIN telefone, JOIN telefone_tipo
ON pessoa_telefone.ID_PESSOA = pessoa.ID
ON pessoa.ENDERECO = endereco.ID
ON telefone.TIPO = telefone_tipo.ID
WHERE pessoa.CPF = 123456
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
richard1994x
  • 27
  • 5

1 Answers1

0

According to the MySQL 5.7 documentation you can use multiple-table delete if not using InnoDB tables (for which there are foreign key constraints).

In this case, the code below should work:

delete pt, p, t, tp, e
from pessoa_telefone pt 
inner join pessoa p on p.id = pt.id_pessoa
inner join telefone t on t.id = pt.id_telefone
inner join telefone_tipo tp on tp.id = t.tipo
inner join endereco e on e.id = p.endereco
where p.cpf = '123456';

If you are using InnoDB (tables for which there are foreign key constraints), again according to the MySQL 5.7 documentation: "you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly".

Also, there are many other questions like that on stack overflow. See this (already marked as duplicated).

Renato Amaral
  • 311
  • 1
  • 2
  • 10
  • I'm using InnoDB with foreign key that show the diagram, but the ON DELETE is RESTRICT. Changing the ON DELETE is not a option to CASCADE. – richard1994x Nov 12 '20 at 03:37