0

I searched a lot and nothing could fit me. I am looking for a way to select or delete rows without removing everything. Check below to understand!

username ip_address

a 127.0.0.1
b 127.0.0.2
c 127.0.0.3
d 127.0.0.4 < duplicate keep
d 127.0.0.4 < duplicate remove
e 127.0.0.5
d 127.0.0.4 < duplicate remove

What it should select/delete:

username ip_address

d 127.0.0.4
d 127.0.0.4

EDIT:

I have found a solution by making a new temporary table and duplicating the rows there.

ouflak
  • 2,458
  • 10
  • 44
  • 49
Tesla
  • 169
  • 1
  • 6
  • 1
    Does this answer your question? [Delete all but one duplicate record](https://stackoverflow.com/questions/5882649/delete-all-but-one-duplicate-record) – Jesse Nov 28 '21 at 02:01
  • Also, it sounds like you should probably be putting a `UNIQUE` constraint on that column. – Jesse Nov 28 '21 at 02:02
  • @Jesse It does not as i need to match the username and the ip_address, Not only 1 unique id. – Tesla Nov 28 '21 at 02:20
  • @Tesla, you can have a constraint on a multiple columns, for example username,ip_address, or ip_address,username – gview Nov 28 '21 at 02:44
  • @gview I'm not sure how to do that, Could you provide some example please? – Tesla Nov 28 '21 at 02:55
  • ALTER TABLE your_table ADD CONSTRAINT your_table_username_ip UNIQUE KEY(username, ip_address) – gview Nov 28 '21 at 09:09
  • Keep in mind you have to remove the duplicates in order to create the constraint. After you've created it, it will disallow duplicate username,ip_address combinations – gview Nov 28 '21 at 09:10
  • @gview and how i remove the duplicates? – Tesla Nov 28 '21 at 10:24

0 Answers0