# Delete rows from table with no primary key, SQL

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.

## Answers

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

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));

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.

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)

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.