deleting a row with duplicate title with two or more condition

Question

I have a table with duplicate title and I can query them just fine when trying to find.

Here's the table and code

TABLE NAME: documents

+---------- +--------+
| module_srl| title  |
+---------- +--------+
| 6225      | test21 |
| 6225      | test   |
| 6225      | test   |
| 6226      | test3  |
| 6226      | test5  |
| 6226      | test6  |
+-----------+--------+

SQL QUERY:

SELECT title
FROM `documents`
WHERE module_srl = 6225
GROUP BY title
HAVING COUNT( * ) >1
ORDER BY `documents`.`title` DESC

THIS SHOWS RESULT OF 1 which has 1 duplicate found (I guess)

Title
test

How do I delete "only" the duplicate title that matches module_srl = 6225 without deleting the original.

you don't have primary key or unique key on this table? If not, you need add one. — Jacob, Jul 21 '18 at 09:24
I tried this but it gives me error : DELETE FROM documents WHERE module_srl='6225' and title not in ( SELECT min(title) as min_title FROM documents WHERE module_srl='6225' GROUP BY title ); — danny, Jul 21 '18 at 10:22
tried to run that query and i get this error, that's the best i came up with ='( You can't specify target table 'documents' for update in FROM clause — danny, Jul 21 '18 at 10:25
yes its document_srl but module_srl is like category in my table so I would like to delete duplicates using category and title. — danny, Jul 21 '18 at 10:39
Possible duplicate of [Remove duplicate rows in MySQL](https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Progman, Jul 21 '18 at 10:44

score 0 · Accepted Answer · answered Jul 21 '18 at 10:57

You need the column identifier to differentiate the duplicate column.

The below query can delete any duplicate, instead of limit to module_srl 6255. if you want to limit to 6255, you can edit to add the where clause.

SqlFiddle

delete from documents
where document_srl in (
    select document_srl
    from (
        select module_srl, min(document_srl) as document_srl
        from documents
        group by module_srl, title
        having count(*) > 1
    ) t

deleting a row with duplicate title with two or more condition

1 Answers1