Hi, I have two tables with the structure and sample data like –
Table mapping -
id | user_id | role_id
1 | 5 | 3
2 | 5 | 4
3 | 6 | 4
4 | 6 | 5
5 | 6 | 3
6 | 6 | 6
7 | 7 | 5
Table role -
id | role
3 | ‘admin’
4 | ‘admin’
5 | ‘admin’
6 | ‘manager’
with the relation -> mapping.role_id=role.id
I want to delete all the entries from mapping table where an user_id is mapped to role_id having same roles, for example after deletion I would like to have something like –
Table mapping -
id | user_id | role_id
1 | 5 | 3
5 | 6 | 3
6 | 6 | 6
7 | 7 | 5
I initially thought of something where I join both the tables and delete all the entries having role.id with same roles but I am unable to get optimised solution without using a lot of joins (~3). Can someone please help me here?
submitted by /u/kartik_579
[link] [comments]
Go to Source of this post
Author Of this post: /u/kartik_579
Title Of post: What is the best way to delete entries from one table on basis of duplication in its foreign dependency table?
Author Link: {authorlink}