Pages

Wednesday, September 24, 2014

Duplicate values in a table


Create table sql :

 CREATE TABLE ABC (  ID NUMBER , NAME VARCHAR2(20 BYTE) ) ;

Lets insert duplicate values into it:

INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('11', 'B');
INSERT INTO ABC (ID, NAME) VALUES ('11', 'B');
INSERT INTO ABC (ID, NAME) VALUES ('13', 'G');
INSERT INTO ABC (ID, NAME) VALUES ('13', 'G');

Now table looks like this:

                  

 Now we have a duplicate combination of 2 columns.
sql to find duplicate values combination in two columns:
 
 select ID,NAME, count(ID) from ABC group by ID,NAME having count(ID) > 1;

sample output:


 


Find the duplicate ID's in a column :

 select ID, count(ID) from ABC group by ID having count(ID) > 1;


Sample output:



 Retrieving duplicate values in database :


SELECT a.*
FROM   tablename a
 INNER
  JOIN 
  (
  SELECT column1
       , column2
  FROM   table1 
  GROUP
      BY column1
       , column2
  HAVING Count(*) >1
  ) b
    ON a.column1 = b.column1
   AND a.column2 = b.column2



If you have combination of multiple columns that you want to check duplicates. 

For example : Check duplicates for combination of multiple columns check this link.

http://arvindasdba.blogspot.com/2016/02/check-duplicates-for-combination-of.html



No comments :

Post a Comment