CODEWITHSUNDEEP
mysqldelete-row
markus
markus

Reputation: 11

Mysql Deleting duplicates with following ids

I have a table "c31805" which looks like this:

| Id3| COL 1  |<br/>
|----|--------|<br/>
| 1  | trial1 |<br/>
| 2  | x142   |<br/>
| 3  | left   |<br/>
| 4  | left   |<br/>
| 5  | left   |<br/>
| 6  | trial2 |<br/>
| 7  | x139   |<br/>
| 8  | left   |<br/>
| 9  | left   |<br/>
| 10 | trial3 |<br/>
|----|--------|<br/>

Is there a way to delete the duplicates in this case “left” which follow each other directly. But not the ones following later. The end result should look like this:

| Id3| COL 1  |<br/>
|----|--------|<br/>
| 1  | trial1 |<br/>
| 2  | x142   |<br/>
| 3  | left   |<br/>
| 6  | trial2 |<br/>
| 7  | x139   |<br/>
| 8  | left   |<br/>
| 10 | trial3 |<br/>
|----|--------|<br/>

I tried a select to filter out the duplicates:

`SELECT `Id3` 
from c31805 a1 
where 0 < (select count(*) from c31805 a2 where a2.Id3 = a1.Id3 + 1 and a2.`COL 1` = a1.`COL 1`);

This seems to work.

Then I tried to add a delete:

DELETE FROM c21705 
where id3 in (SELECT `Id3` from c21705 m1 where 0 < (select count(*) from c31805 m2 where m2.Id3 = m1.Id3 + 1 and m2.`COL 1` = m1.`COL 1`));

But this causes an error message:

”#1093 - Table 'c21705' is specified twice, both as a target for 'DELETE' and as a separate source for data ”

Is there a way to achieve this goal.

Upvotes: 1

Views: 48

Answers (2)

user5480949
user5480949

Reputation: 1668

The query below will keep duplicate values with the lowest Id3 value

DELETE FROM c31805
WHERE Id3 NOT IN (SELECT Id3 FROM t
                        ( SELECT [COL 1], MIN(Id3) as Id3
                          FROM c31805
                          GROUP BY [COL 1]
                          ) t

Upvotes: 0

ahmad
ahmad

Reputation: 2729

I think you'll have to use an alias to avoid confusion,

Your query would look like this.

DELETE `t` FROM c21705 as `t` where t.id3 in (SELECT Id3 from c21705 m1 where 0 < (select count(*) from c31805 m2 where m2.Id3 = m1.Id3 + 1 and m2.COL 1= m1.COL 1));

Upvotes: 1

Related Questions