Delete from same table using inner join seems to be failing (both records removed)

Question

I have a table which has both ChildID and ParentID. Due to the way the data was compiled, in some cases those values are reversed. So I wrote what I thought was a simple Inner Join where both values are reversed and tried to delete the first tables value. I seem to remember this working fine before except now this del

Delete S1 from TableA S1
Inner Join TableA S2 
On S1.ChildID=S2.ParentID
and S1.ParentID=S2.ChildID

https://sqlfiddle.com/mysql/online-compiler?id=bc5a98e7-efd9-443c-87d9-3eba3304da82

Answer 1

it depends which column you will keep.

from that data it is imppssible to tell.

but i comews to you need to differentiate which between the IDs, that determines which column will be deleted

-- INIT database
CREATE TABLE TableA (
  ID  INT,
  ChildID INT,
  ParentID INT
);



INSERT INTO TableA (ID,ChildID,ParentID) VALUES (1,89,891);
INSERT INTO TableA (ID,ChildID,ParentID) VALUES (2,99,991);
INSERT INTO TableA (ID,ChildID,ParentID) VALUES (3,991,99);
-- QUERY database

Delete S1 from TableA S1
    Inner Join TableA S2 
    On S1.ChildID=S2.ParentID
    and S1.ParentID=S2.ChildID
    AND S1.ID < S2.ID;
Select * from TableA;

ID	ChildID	ParentID
1	89	891
3	991	99

INSERT INTO TableA (ID,ChildID,ParentID) VALUES (2,99,991);

Delete S1 from TableA S1
    Inner Join TableA S2 
    On S1.ChildID=S2.ParentID
    and S1.ParentID=S2.ChildID
    AND S1.ID > S2.ID;
Select * from TableA;

ID	ChildID	ParentID
1	89	891
2	99	991

fiddle