0

What I'm looking to do, is delete all duplicated where column A and column B are duplicate. For example:

A      B      C   
-----------------
Apple  Pear   11  
Apple  Pear   12  
Apple  Pear   13  
Orange Apple  22  
Orange Beer   21  
Cinder Punch  30  
Cinder Punch  31  
Cinder Punch  32

Would result in:

A      B      C   
-----------------
Apple  Pear   11  
Orange Apple  22  
Orange Beer   21  
Cinder Punch  30
Improve this question
2
  • 1
    Possible duplicate of stackoverflow.com/questions/1799518/… Commented Jul 3, 2011 at 22:15
  • @skaffman: There are countless duplicates -- do you not agree there should be a tag to make it easier to find them? Commented Jul 3, 2011 at 22:58

2 Answers 2

Reset to default
3

Step 1: Move the non duplicates (unique tuples) into a temporary table

CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

HERE, [column to remove duplicates by] = column names seperated by "COMMA", so in your case A,B

Step 2: delete delete the old table We no longer need the table with all the duplicate entries, so drop it!

DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Won't work in this case, the problem is those two rows arent key rows and if I group them i'll end up having multiple rows still. I'll make sure i change my question.
so you mean that A and B are seperate rows, and you have included some numbers as well, is that a third row ?
No A|B|C are all different columns. I have to leave 1 row of matching A|B's regardless of C.
@Mike Edited answer to make it a solution for your updated problem.
can we do........select * from table group by [column to remove duplicates by]-- means not all columns .... my question is - can we select simply 'c' also without putting 'c' in group by clouse ??
|
0

you can do as follow to achive this

delete from table_name
where rowid not in (select min(rowid) from table_name group by a,b)
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.