Delete in more than one table with an SQL?

Question

I’m trying to apply a DELETE in more than one table, tables have a column (not of the same name) with the same attributes. I want to delete through ID. But I have the table usuario with column id and areausuario column-shaped idUsuario, are the same attributes (id = 1, IdUsuario = 1).

I was trying with this code.

DELETE us.*
FROM usuarios us
INNER JOIN disponibilidade ds ON (us.id = ds.IdUsuarios)
INNER JOIN areausuarios au ON (ds.IdUsuarios = au.IdUsuarios)
INNER JOIN gestor g ON (au.IdUsuarios = g.id)
WHERE g.id = 7;

But this code is only deleting from one table.

I tried some others but he applies the error 1451, where I can’t delete the usuario, without first removing it from the table areausuarios.

Answer 1

ON DELETE CASCADE.

The message that says Voce cannot "delete the user without first removing it from the table areausuario" indicates that there is a relationship between the two tables through a foreign key.

What you have to do is just that, first delete the user from the table areausuario and only then delete from the user table. In most Voce databases you can set the relationship so that when you delete a record in the "parent" table, automatically the corresponding records will be deleted in the "child" table".

See how it is done in Mysql at that link.

Answer 2

Are your tables related? There is a foreign key in the column IdUsuario on the table areausuario?

If yes, you can use on delete cascade, as explained by @Sidon.

---

Otherwise, you can execute two distinct queries and then effect the commit.

The concept of transaction to execute these distinct queries and finally execute the commit.

The question has the tags java and mysql, but has no Java code in its body. This makes it difficult to explain in detail how to do it, but here in have an example of how to use transaction with Java and Mysql.