MySQL: How can I delete two rows in a junction table based on a specific ID?-CodePudding

I have a Mysql junction table user_connections which maps the users table with the following columns: user_from and user_to, both are foreign keys in users table. user_from and user_to are both primary keys therfore a parttern like (1,2) and (1,2) can never repete.

user_connections table.

create table user_connections (
  user_from int,
  user_to int,
  primary key(user_from, user_to)
  );
  
  insert into user_connections(user_from, user_to) values(1, 2);
  insert into user_connections(user_from, user_to) values(2, 1);
  insert into user_connections(user_from, user_to) values(67, 1);
  insert into user_connections(user_from, user_to) values(68, 1);
  insert into user_connections(user_from, user_to) values(69, 1);
  insert into user_connections(user_from, user_to) values(70, 1);

How it looks after insertion

Query #1

select * from user_connections;

user_from	user_to
1	2
2	1
67	1
68	1
69	1
70	1

View on DB Fiddle

My question is how can I delete the pattern (1,2) and (2,1) with one query based on ID 2. Also if I had lets say another pattern like (67, 2) and (2, 67), how would I delete those patterns?

Thank you in advance.

CodePudding user response：

I got the solution. I just need to use WHERE IN as follow:

DELETE FROM user_connections
WHERE (user_from, user_to) in ((1, 2), (2, 1))

CodePudding user response：

Assuming you have a user table with an ID linked to the 2 columns you describe here. A request like this could do the job :

DELETE uc
FROM user_connections uc
INNER JOIN user u
    ON u.id = uc.user_from
    OR u.id = uc.user_to
WHERE u.id = 2;