Search code examples
mysqlinner-join

MySQL : delete all records based on an id and other colums content

I got the following table

+----+---------+---------+
| id |  val1   |  val2   |
+----+---------+---------+
|  1 |  aaaa   |  bbbb   |
|  2 |  cccc   |  dddd   |
|  3 |  aaaa   |  eeee   |
|  4 |  ffff   |  bbbb   |
|  5 |  aaaa   |  bbbb   |
+----+---------+---------+

I have identified I need to delete the record with id = 1

But I want also delete all the records having same val1 and val2 as id 1.

In this case, id 5 should also me removed.

I have tried that with no luck

SELECT id FROM table t1
INNER JOIN table t2
ON t1.val1 = t2.val1
AND t1.val2 = t2.val2
AND t1.id = 1
Solution

  • you could use a subquery 

    delete t
from table t
inner join  (
  select val1, val2
  from table 
  where id = 1 
) s on s.val1 = t.val1 
      and s.val2 = t.val2