------------------------------------------------**Handling duplicate
records**----------------------------------------------------------
Finding duplicates:
-->if count(*) >1 then the records has duplicates
>> SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
---------------------------------------------------------------------------------
find unique records?
>> SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) = 1;
-----------------------------------------------------------------------------------
write a query to remove duplicate records ?
1)using rowid:
>>delete from table_name
where rowid not in
(select max(rowid) from table_name
group by col1,col2,col3...)
2) using distinct:
>>select distinct * from table_name?
or
>> create new_table as select distinct * from old_table
note: distinct method is not preferble because it is costly excution (it will
filter all the records)
-----------------------------------------------------------------------------------
----------------------------------------------------------
what is rowid and what is use of rowid?
note --> rowid is availble in only Oracle
rowid:
- It is a pseudo column that provides a way to directly access a row.
- represents the unique address or identifier for a specific row in a table.
>> select id,name,sal,rowid from emp_dup;
id name sal rowid
100 bala 50000 AAAR5MAAEAAAJpbAAA
200 swati 80000 AAAR5MAAEAAAJpbAAB
100 bala 50000 AAAR5MAAEAAAJpbAAC
300 ramu 50000 AAAR5MAAEAAAJpbAAD
400 shyam 50000 AAAR5MAAEAAAJpbAAE
500 bala 10000 AAAR5MAAEAAAJpbAAF
note - rowid is unique address to each rows (for duplicates also there will be
different rowid address)
>>select max(rowid) from emp_dup;
AAAR5MAAEAAAJpbAAF ---> this will display max rowid (last
column)
how to remove duplicates using rowid?
>>select max(rowid) from emp_dup
group by id,name,sal; ---> this will disply only unique
records and its row id
AAAR5MAAEAAAJpbAAC
AAAR5MAAEAAAJpbAAD
AAAR5MAAEAAAJpbAAB
AAAR5MAAEAAAJpbAAF
AAAR5MAAEAAAJpbAAE
>>delete from emp_dup
where rowid not in ---> this will delete the
duplicate records
(select max(rowid) from emp_dup
group by by id,name,sal)
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
----------------------------------------
how do you handle duplicate records?
primary key --> Use primary keys or unique constraints to prevent the insertion of
duplicate records.
find duplicates--> Detect duplicates using SELECT and GROUP BY statements to
identify instances based on specific columns.
remove duplicates --> remove duplicates uisng rowid or distinct
querying -->Use DISTINCT in SELECT statements to retrieve only unique records when
querying data.
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------
