0

Lets say you have a table like this

users_cities_relations

|--id--|name|c-id|
|----------------|
|  1   |Tom | 2  |
|  2   |Mike| 3  |
|  3   |Eve | 2  |
|  4   |Tom | 2  |

As you can see the user Tom exists 2 times with the same c-id.

Now what I'm trying to do is to delete one of the rows.

I was thinking of something like :

SELECT id FROM users u1, users u2
WHERE u1.name = u2.name
AND u1.c-id = u2.c-id;

And then delete it with a diffrent statement.

This sadly does not return what I need, if I remember it right, this returned all the id's.

rokkz
  • 179
  • 1
  • 1
  • 13
  • possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) – Marc B Mar 09 '14 at 02:24
  • this is no duplicate, all the other posts check for one same value, this is to check for two same values. – rokkz Mar 09 '14 at 02:25
  • so? the basic concept remains the same. Find dupes, delete all but one. Just because you have TWO values that need to be checked doesn't magically make it any different. – Marc B Mar 09 '14 at 02:27
  • if you start like that, you can go and mark all posts as possible duplicated – rokkz Mar 09 '14 at 02:29
  • No, but it does boil down to a large number of questions pretending they're different because of minor little detail. if this was a cooking site, should we have two different recipes for christmas cookies because one calls for red food coloring and the other calls for red? – Marc B Mar 09 '14 at 02:31
  • a more simple approach for finding duplicates can be found here http://stackoverflow.com/a/688551/1815624 – CrandellWS Jul 01 '14 at 16:19

1 Answers1

1

Try this approach 

Delete u from users_c u , (
select max(id) as id, name, c_id
from users_c
group by name, c_id
having count(id) > 1)  as temp
where  u.id = temp.id and u.name = temp.name and u.c_id = temp.c_id;

OR ith temp table to delete duplicate records

CREATE TEMPORARY TABLE temp_users(
id  int,
name  varchar(40),
c_id int

);

insert into temp_users
select max(id), name, c_id
from users_c
group by name, c_id
having count(id) > 1;

select * from users_c;
select * from temp_users;

delete u from users_c u, temp_users temp 
where  u.id = temp.id and u.name = temp.name and u.c_id = temp.c_id;

drop temporary table temp_users;
user3393753
  • 86
  • 3