Question 21880 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search