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.


Need Your Help

how to list fonts installed from Chrome extension

flash google-chrome fonts google-chrome-extension typeerror

I'm looking for a way to get the list of fonts installed on a computer from a chrome extension. I found no function like EnumerateAllFonts({}) from Firefox in chrome and when I tried to embed a swf

MEF (.NET) analogue for Java

java dependency-injection mef

I got very used to this clean syntax with MEF in .NET

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.