Delete a record from different tables

Question

Could you please help me solve this problem: Im having 3 Tables:

Table Name1: Contact

cnt_id, CNTName

12,     test
13,     test2


Table Name2: Relation

cnt_id, r_grpid

12,     55
13,     55
13,     56

Table Name3: Group

grp_1d, 

55
56

What Im looking to do is when i delete the group id 55 from the table GROUP the records on table CONTACT delete accordingly. But as you can see CNT_id 13 is linked to another group as well in this case CNT_id 13 Should NOT be deleted.

Regards

Delete cascade is one of the ways to do this. See: https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server — KC Wong, Jul 27 '17 at 09:33
AFAIK, Delete cascade will only delete the record in the relation table, it will not delete the records in the contact table as well... — Zohar Peled, Jul 27 '17 at 09:36
Well if its belong to another group then just skip deleting from the table contact. — iman, Jul 27 '17 at 09:41

score 2 · Answer 1 · answered Jul 27 '17 at 09:48

2

If you suppose to use a query or stored procedure , This will be helpful.

declare  @groupId  int =55

delete from  Contact where cnt_id in  (select C.cnt_id from Contact C
inner  join Relation R  on R.cnt_id=C.cnt_id  where R.r_grpid =@groupId)

delete from   Relation where  r_grpid =@groupId

delete from  Group where  r_grpid =@groupId

answered Jul 27 '17 at 09:48

Adikari Nadeesha

322
3
12

Thank you so much its working grate just what about if in table relation another group was linked to the same contact id. IS there a way to skip the deleting the number in this case? – iman Jul 27 '17 at 10:27

deepakl · Answer 2 · 2017-07-27T12:53:35.187

May be a trigger can help you, I did following in sql developer:

insert into contact values (12, 'abc');
insert into contact values (13, 'def');

insert into relation values (12, 55);
insert into relation values (13, 55);

insert into grouptab values (55);

Then opened a session in sqlplus

created a trigger

SQL> CREATE OR REPLACE TRIGGER delete_data
  2  BEFORE delete
  3   ON grouptab
  4  REFERENCING NEW AS New OLD AS Old
  5  FOR EACH ROW
  6  DECLARE
  7  BEGIN
   delete from contact where contact_uid in (select contact_uid from relation where group_uid = :old.GROUP_uID);
END delete_data;

SQL> select * from contact;

CONTACT_UID CONTACT_NAME
----------- ------------------------------
         12 abc
         13 def

SQL> select * from relation;

CONTACT_UID  GROUP_UID
----------- ----------
         12         55
         13         55

SQL> select * from grouptab;

 GROUP_UID
----------
        55

SQL> delete from grouptab;

1 row deleted.

SQL> delete from contact;

0 rows deleted.

Please try following:

delete from relation rel where group_uid in (55) and 1 = (select count(*) from relation where contact_uid = rel.contact_uid);

Hi, Thank you for your answer but its seems to be very complicated and it doesnt do what im expecting to get. The second answer is almost giving what im looking for if you can edit it i appreciate that. — iman, Jul 27 '17 at 12:00

score 0 · Accepted Answer · answered Jul 27 '17 at 13:53

If the combination of cnt_id and r_grpid in Relation is unique, you can use the query suggested before but with an additional filter on the cnt_id, like this:

declare  @groupId  int =55

delete from  Contact where cnt_id in  (select C.cnt_id from Contact C
inner  join Relation R  on R.cnt_id=C.cnt_id  where R.r_grpid =@groupId AND cnt_id not in (select cnt_id from Relation group by cnt_id having count(*) > 1))

delete from   Relation where  r_grpid =@groupId AND cnt_id not in (select cnt_id from Relation group by cnt_id having count(*) > 1)

delete from  Group where  r_grpid =@groupId

Delete a record from different tables

3 Answers3