0

How we can delete multiple rows having columns from a db? Suppose db has following data (id, list_name, user) and list_name has values as:

 Owner-aaa
 coowner-aaa
 owner-aaa
 subowner-aaa

How we can delete the rows having "Owner-aaa" and "owner-aaa" which are duplicates?

Can we add something in this query here:

delete from <table_name> where list_name = 'owner-aaa'

But it deletes only lower case list, I want something general which checks duplicates in small and caps and delete both of them?

Thanks in advance Amy

Improve this question
6
  • you want to delete both Owner-aaa and owner-aaa, or you want to keep just one and delete all of the others? Commented Jun 28, 2013 at 14:12
  • or do you want to delete all duplicated rows?? Commented Jun 28, 2013 at 14:13
  • I wanted to delete all rows... -Amy Commented Jun 28, 2013 at 14:42
  • Another alternative: change to a case-insensitive collation, and your original DELETE statement would work just fine... It would also allow you to use some of the query strategies defined in the answers without having to use UPPER() or LOWER(), which means that indexes could be used directly in the query, leading to vastly faster performance (especially if your table has many rows). Commented Jun 28, 2013 at 15:15
  • @Curt Thanks, I was trying with like I am not sure how syntax should be. Can you help me with the query too, if possible. Commented Jun 28, 2013 at 15:21

5 Answers 5

Reset to default
1 
DELETE FROM mytable WHERE LOWER(listname) IN
(SELECT LOWER(listname) FROM mytable 
GROUP BY LOWER(listname)
HAVING COUNT(*) > 1)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

mysql usually ignores case when grouping, but doesn't allow you do use an IN clause in a delete query
That would work, but would remove all records for which a duplicate exists (i.e., it would not leave one)
Yeah, I am not sure about MySQL, and yes, it removes all dupes, which is how I interpreted the question.
1 
delete from tableName where LOWER(list_name) = 'owner-aaa'
Improve this answer

2 Comments

Whoever downvoted, OP said nothing about keeping rows, just to delete rows no matter if values are upper or lower case.
OP said they want something general, this has a hard-coded value in it.
0

Meaby you can use LOWER/UPPER sql functions.

But are you sure your model is correct? It seem realy weird to have a name list like that. That should be another table NAMES with ID and NAME field. It's a 1-N relation.

Improve this answer

Comments

0

I'm not entirely sure from your question whether you want to delete all rows where duplicates occur, or leave one, and remove only the true duplicates. So here's a shot at each:

To remove only the true duplicates:

DELETE FROM MyTable WHERE id IN
(
   SELECT T1.id 
     FROM MyTable T1
          INNER JOIN MyTable T2
             ON UPPER(T1.list_name) = UPPER(T2.list_name)
            AND T2.id <> T1.id
            AND (T1.id <> (SELECT MAX(id) FROM MyTable WHERE UPPER(list_name) = UPPER(T1.list_name))
) DUPS

This presumes that the id field is unique to each record

To remove all records where there are duplicates, remove the two "AND" clauses in the subquery.

Improve this answer

1 Comment

I want to delete all rows eg. owner-aaa and Owner-aaa both irrespective of the total no count.
0 
DELETE a
FROM list a
INNER JOIN list b ON LOWER(a.list_name)=LOWER(b.list_name)
WHERE a.id <> b.id
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.