Delete rows in MySQL matching two columns in another table

Question

I am using MySQL and would like to delete entries from table T1:

user_id  level_id  other_data
   1        5         ...
   2        7         ...
   :

where the user_id and level_id values appear together in table T2:

user_id  level_id
   1         5
   2         6

In this example, the first row would be deleted from table T1.

I tried:

delete from T1 where (user_id,level_id) in select user_id,level_id from T2;

but that has a syntax error.

Gordon Linoff · Accepted Answer · 2014-08-14 19:33:55Z

8

You are pretty close. Try using exists:

delete from T1
    where exists (select 1
                  from t2
                  where t1.user_id = t2.user_id and t1.level_id = t2.level_id
                 );

answered Aug 14, 2014 at 19:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ellen Spertus Over a year ago

Thanks, both for the solution and the encouraging words. I hadn't known that "select 1" pattern.

Gordon Linoff Over a year ago

@espertus . . . Some databases (such as Postgres) actually accept a syntax very close to what you wrote. All subqueries should be in parentheses, but otherwise Postgres would accept it.

Collectives™ on Stack Overflow

Delete rows in MySQL matching two columns in another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related