Delete rows from table with no primary key, SQL

Question

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.

Answer 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