CODEWITHSUNDEEP
mysqlsql
Ellen Spertus
Ellen Spertus

Reputation: 6805

Delete rows in MySQL matching two columns in another table

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.

Upvotes: 4

Views: 4805

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 8

Related Questions