CODEWITHSUNDEEP
mysqlsqlsql-delete
Nando
Nando

Reputation: 67

Delete relational query Mysql

I need fix this query:

DELETE t1, t2, t3
FROM roles as t1
JOIN user_role as t2 on t1.ROLEID = t2.ROLEID
JOIN role_perm as t3 on t1.ROLEID = t3.ROLEID
WHERE t1.ROLEID = $role_id"

"user_role" and "role_perm" are in relation with roles, and they have DELETE ON CASCADE.

But when I tried to delete a row, if I have not added data in user_role or role_perm, row cannot be deleted, only when I have added data in three tables, the row is deleted.

Upvotes: 1

Views: 170

Answers (4)

Ray
Ray

Reputation: 41448

Your joins are inner joins, so only when data is in all three tables will you have a result to delete (the result set of your joins).

Assuming you want to do the following:

  • Delete the a role roles.ROLEID = <some_role_id>
  • Delete all user_role records with user_role.ROLEID = <some_role_id>
  • Delete all role_perm records with user_role.ROLEID = <some_role_id>

Convert them to left outer joins, with the first table the main table all others depend on. 

DELETE t1, t2, t3
 FROM roles as t1
   LEFT JOIN user_role as t2 on t1.ROLEID = t2.ROLEID
   LEFT JOIN role_perm as t3 on t1.ROLEID = t3.ROLEID
 WHERE t1.ROLEID = $role_id

The above will work without the cascade foreign key constraint. If you've the cascade and assuming it's on Foreign keys tied to roles.ROLEID you'll only need DELETE t1 FROM... as the delete will cascade to all other tables.

Thus if both user_role and role_perm have cascading FK's on roles.ROLEID:

  DELETE FROM roles WHERE t1.ROLEID = $role_id;

Now, I run like the plague from cascading FK's and prefer the explicit delete of the first query. Have had too many unexpected cascading deletes in my life I guess.

Upvotes: 2

M Danish
M Danish

Reputation: 478

You are using inner join to delete data from Table. If the data do not exist in child table inner join ignore the row that's why was row not deleted.

Try to delete using left join or in only parent table if delete cascade active on all tables

Upvotes: 0

user8406805
user8406805

Reputation:

You don't need to delete the rows from child tables, "user_role" and "role_perm" as you said there is DELETE ON CASCADE.

All you need to do is, delete the rows from main/parent table, then child table rows automatically get deleted due to DELETE ON CASCADE.

Try below SQL:

DELETE roles
WHERE ROLEID = $role_id

Upvotes: 0

FDavidov
FDavidov

Reputation: 3675

From the logical point of view, the join between the three tables creates a LOGICAL table that then is acted on by your command.

Since you have no data in all three tables, the joins return an empty result and hence nothing to delete.

From the programming point of view, what you are trying to do is not very recommendable.

Upvotes: 0

Related Questions