1

Sorry I'm just using this account from my old friend, he said he wanted it to have higher reputation. lol

I have a table that looks like this and I want to delete the entries wherein the next duplicate ones based from the first_name, middle_name, and last_name, and alias columns. While keeping the first entry or the ones who have the lowest possible id enter image description here

TheBAST
  • 2,680
  • 10
  • 40
  • 68
  • Add an example to your example. It will clarify what do you want exactly. – Nitin Bisht Apr 03 '20 at 06:00
  • 1
    For starting your post with "Sorry I'm just using this account from my old friend, he said he wanted it to have higher reputation. lol", i downvoted. Also you should not post pictures for such a problem. This problem is easily explained in words, while a DDL and some example data CAN be usefull. – Luuk Apr 03 '20 at 06:28
  • Ok sir , I upvote u – TheBAST Apr 03 '20 at 06:53

1 Answers1

1

You can do it with a self join in the delete statement:

delete t
from tablename t inner join tablename tt
on tt.first_name = t.first_name and tt.last_name = t.last_name and tt.alias = t.alias  
and tt.id < t.id;

or by deleting all the rows with ids other than the min ids of each combination of first_name, last_name and alias:

delete from tablename
where id not in (
  select t.minid from (
    select min(id) minid
    from tablename
    group by first_name, last_name, alias
  ) t
)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • `Delete C1 from criminals C1 inner join criminals C2 where C1.id > c2.id and C1.first_name = C2.first_name and C1.last_name = C2.last_name and C1.alias = C2.alias;` I used my own query for finishing this but I get ```#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mixx`.`criminal_profiles`, CONSTRAINT `criminal_infos_criminal_id_foreign` FOREIGN KEY (`criminal_id`) REFERENCES `criminals` (`id`))``` – TheBAST Apr 03 '20 at 10:05
  • Change `where` to `on`. – forpas Apr 03 '20 at 10:07
  • what do u mean brother ? – TheBAST Apr 03 '20 at 10:08
  • Your code uses `where` it should be `on` because there is a join. But now that I see all your comment there is a constraint in the table `criminal_profiles` because the column `criminal_id` references the `id` of `criminals` so you can't delete. – forpas Apr 03 '20 at 10:12
  • How do I enable them to delete – TheBAST Apr 03 '20 at 10:16
  • 1
    Check this: https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql – forpas Apr 03 '20 at 10:19