Delete in more than one table with a SQL?

Question

Delete in more than one table with a SQL?

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I'm trying to apply a DELETE to more than one table, tables have a column (not with the same name) with the same attributes. I want to delete via ID. But I have the table usuario with column id and areausuario with column 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 deleting only from a table.

I've tried a few more but it applies the error 1451 , where I can not delete the usuario , without first removing it from the areausuarios table. >

mysql java

asked by anonymous 05.05.2017 / 02:48

2 answers

How to make an HTML function stop working with JavaScript? How to read and block this TXT with PHP?

score 1 · Answer 1

ON DELETE CASCADE.

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

What you have to do is exactly that, first delete the user from the areausuario table and then delete it from the user table. On most banks you can configure the relationship so that by deleting a record in the "parent" table, the corresponding records are automatically deleted in the "child" table.

See how it's done in Mysql in this link .

score 1 · Answer 2

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

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

Otherwise, you can run two distinct queries and then do commit .

You can use the concept of transaction to execute these distinct queries and finally execute commit .

The question has the tags java and here has an example of how to use transaction with Java and MySQL.