3

I am trying to delete several rows from a table table in SQL. The problem is that I can't figure out how to delete from that table using the result of a subquery, as there is no primary key in that table. The structure of the tables is as follows:

Friend ( ID1, ID2 )

The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123). 

Likes ( ID1, ID2 )

The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present. 

(No primary key)

The situation I am trying to solve is:

"If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple."

Thanks in advance.

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
Rijul
  • 515
  • 7
  • 19

5 Answers5

1

In most SQL dialects you can do:

delete from likes
    where not exists (select 1 from likes l2 where l2.id1 = likes.id2 and l2.id2 = likes.id1) and
          exists (select 1 from friends f where f.id1 = likes.id1 and f.id2 = likes.id2);

This is pretty much a direct translation of your two conditions.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Why are you joining to friends? Surely the non existence can be established with even the friends table existing at all. – Bohemian Aug 23 '13 at 01:00
  • @Bohemian . . . That satisfies the condition "If two students A and B are friends". The `not exists` clause satisfies "A likes B but not vice-versa". – Gordon Linoff Aug 23 '13 at 01:02
  • The answer given here @GordonLinoff , deletes more rows than required. This is probably because the existence is being checked on the basis of ID1 only, and not the pair of ID1 and ID2. Remember, we cannot uniquely identify a row only by ID1. In this case, we are also deleting likes by ID1 to other people, i suppose. – Rijul Aug 23 '13 at 04:45
  • @user2709226 . . . I only put the check in for one direction (on the friends because you explicitly say that the "friend" relationship goes in both directions. – Gordon Linoff Aug 23 '13 at 11:28
  • I know, @GordonLinoff , but your soln. deletes more rows than required. According to me, you are using likes.id2 in your statement. So, instead of getting the ID group being considered to be deleted, it only uses first ID1, and selects ID2 from a new table. This selects more rows than required. – Rijul Aug 23 '13 at 15:38
  • @user2709226 . . . I think I understand what you are saying. There was a typo in the first subquery. – Gordon Linoff Aug 23 '13 at 15:56
  • See [my answer](http://stackoverflow.com/questions/18392793/delete-rows-from-table-with-no-primary-key-sql/18407440#18407440) with [SQLFiddle](http://sqlfiddle.com/#!7/6eebf/1) for what I mean. It's just not that complicated - the solution is incredibly simple. – Bohemian Aug 23 '13 at 16:17
1

To solve your problem use the below sql query

delete from friend where (ID1,ID2)  not in
(Select f1.ID1,f1.ID2 from friend f1, friend f2 where f1.ID1 = f2.ID2 && f1.ID2 = f2.ID1)

if your database does not support the mulitple column in "in" clause then use the below query

delete from friend where concat(ID1,':',ID2)  not in
(Select concat(f1.ID1,':',f1.ID2) from friend f1, friend f2 
where f1.ID1 = f2.ID2 && f1.ID2 = f2.ID1)

Can we treat the combination of ID1 and ID2 as a composite primary key ?

It is better to always use the primary key into your each table. Please Add a primary key into your table with integer auto increment field and that will solve your problem very easily

Rakesh Soni
  • 10,135
  • 5
  • 44
  • 51
  • The table data is provided and cannot be modified. So, a unique or composite primary key cannot be added or modified. However yes, the pairs of ID1 and ID2 are unique, as a person cannot like another twice. – Rijul Aug 23 '13 at 05:31
  • The issue is that we cannot use multiple column checks for the where clause using "in". – Rijul Aug 23 '13 at 06:18
  • we can add mulitple column in "in" clause please check http://stackoverflow.com/questions/13027708/sql-multiple-columns-in-in-clause – Rakesh Soni Aug 23 '13 at 06:49
  • I think you missed a point here, We have to check for one-way likes, and not one-way friendship – Rijul Aug 23 '13 at 15:44
1

Very Interesting question:-

here i create your tables and try to create same scenario and i found the solution. May it helps you...

 CREATE TABLE friends
  (
     id1 NUMERIC,
     id2 NUMERIC
  );

CREATE TABLE likes
  (
     id1 NUMERIC,
     id2 NUMERIC
  );

INSERT INTO friends
VALUES      (1,
             2);

INSERT INTO friends
VALUES      (2,
             1);

INSERT INTO friends
VALUES      (1,
             3);

INSERT INTO friends
VALUES      (3,
             1);

INSERT INTO friends
VALUES      (3,
             2);

INSERT INTO friends
VALUES      (2,
             3);

INSERT INTO likes
VALUES      (1,
             2);

INSERT INTO likes
VALUES      (2,
             1);

INSERT INTO likes
VALUES      (1,
             3);

INSERT INTO likes
VALUES      (3,
             1);

INSERT INTO likes
VALUES      (2,
             3);

SELECT *
FROM   friends

SELECT *
FROM   likes

SELECT *
FROM   likes A
WHERE  NOT EXISTS (SELECT 1
                   FROM   likes B
                   WHERE  A.id1 = B.id2
                          AND A.id2 = B.id1
                          AND EXISTS (SELECT 1
                                      FROM   friends
                                      WHERE  A.id1 = friends.id1
                                             AND A.id2 = friends.id2));

Your Delete query will be...

 DELETE A
    FROM   likes AS A
    WHERE  NOT EXISTS (SELECT 1
                       FROM   likes B
                       WHERE  A.id1 = B.id2
                              AND A.id2 = B.id1
                              AND EXISTS (SELECT 1
                                          FROM   friends
                                          WHERE  A.id1 = friends.id1
                                                 AND A.id2 = friends.id2));
Hardik Vinzava
  • 968
  • 10
  • 22
  • @user2709226 Let me know...my Answer is helpful to you or not? – Hardik Vinzava Aug 23 '13 at 08:42
  • Thanks for trying @hardikvinzava, But as I already mentioned, my platform is SQLite, so it is using SQL server. It shows a syntax error for using 'A', as we cannot use aliases for the table we're deleting from. – Rijul Aug 23 '13 at 15:31
0

I have tried to develop an answer for this question. It works for my small test data, but please point out if it might be inefficient for bigger data, or how can it be made better with a better solution.

Delete from Likes where
ID1 in (select Q.ID1
from (select x.ID1, x.ID2
from (select A.ID1,A.ID2,B.ID2 as se
from Likes A left join Likes B
on A.ID2=B.ID1) x where x.ID1 <> x.se or x.se is null) Q inner join Friend F
where Q.ID1 = F.ID1 and Q.ID2 = F.ID2 order by Q.ID1)

and ID2 in  (select Q.ID2
from (select x.ID1, x.ID2
from (select A.ID1,A.ID2,B.ID2 as se
from Likes A left join Likes B
on A.ID2=B.ID1) x where x.ID1 <> x.se or x.se is null) Q inner join Friend F
where Q.ID1 = F.ID1 and Q.ID2 = F.ID2 order by Q.ID1)
Rijul
  • 515
  • 7
  • 19
0

Express the two columns as one value by concatentation, then use a NOT IN () of the reverse combination:

delete from likes
where id1 || ' ' || id2 not in (select id2 || ' ' || id1 from likes);

See a live demo on SQLFiddle showing that this query actually works.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thank you for your trial. But the problem asks for an entirely different solution than you have provided. We have to check one-way liking, with Considering whether they are friends or not. We have to delete only when they are friends, but only one likes another. – Rijul Aug 23 '13 at 17:02