Write an SQL Query to
Delete Duplicate records from a table using ROWID.
Answers were Sorted based on User's Feedback
Answer / ravi kant yadav
Delete from table_name where ROWID not in ( select
max(rowid) from table group by duplicate_values_field_name);
Regards
ravi
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / amit shrivastava
Delete from table where rowid not in (select max(rowid)from
table group by column_name);
Delete from table T1 where rowid < (select min(rowid) from
table T2 where T1.emp_name=T2.emp_name);
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / murugaraj.g
HERE COLUMN_NAME IN ( SELECT COLUMN_NAME FROM TABLE D
WHERE D.COLUMN_NAME_1=E.COLUMN_NAME_1
MINUS
SELECT COLUMN_NAME FROM TABLE F
WHERE F.COLUMN_NAME_1 = E.COLUMN_NAME_1
AND ROWNUM = 1);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / parthasarathi
delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField ;
Try this will work for SQL Server and My Sql but not for
MSAccess .....
For MSAccess try the below one
delete from MyTable
where uniqueField not in
(select min(uniqueField) from MyTable T2
where T2.dupField=MyTable.dupField);
The above query also works for SqlServer but not for MySql..
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / parthasarathi
delete tab1 where (col1_dup,rowid) in (select
col1_dup,max(rowid) from tab1 where group by col1_dup having
count(col1_dup)>1);
Is This Answer Correct ? | 0 Yes | 0 No |
1.SELECT ROWID FROM TABLE T1
WHERE ROWID NOT IN (SELECT MIN(ROWID)
FROM TABLE T2 WHERE T1.KEY=T2.KEY);
THIS QUERY IS USEFUL WHEN WE HAVE A KEY VAL.
2.
i.CREATE TABLE NEW AS SELECT DISTINCT * FROM OLD;
ii.DROP TABLE OLD;
iii.RENAME NEW TO ORIGINAL;
THIS IS WHEN WE APPLY TO DELETE DUPLICATE ROWS..
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ankit kumar srivastava
DELETE FROM EMP_a WHERE ROWID NOT IN(
(SELECT MAX(ROWID) FROM EMP_a a GROUP BY
EMPNO,ENAME,sex,doj,sal,deptno,mgr HAVING COUNT(ROWID)
=1)
UNION
(SELECT MAX(ROWID) FROM EMP_a a GROUP BY
EMPNO,ENAME,sex,doj,sal,deptno,mgr HAVING COUNT(ROWID)>1))
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / srinivas
delete from emp where rowid not in(select max(rowid) from
emp group by sal);
and
delete from(select distinct sal from emp) where rowid>=1;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ajith
DELETE FROM EMP E1
WHERE E1.ROWID>(SELECT MIN(E2.ROWID)
FROM EMP E2
WHERE E1.EMPNO=E2.EMPNO)
Is This Answer Correct ? | 0 Yes | 0 No |
Is SOB is attached with Business grp ?
Can u suggest me the best institute for Oracle apps Technical in Hyderabad?
where we find the status of order information.
Hi Friends this is preetham, i am searching for job on oracle apps(technical) i put 3 years fake exp, so any one please could you help me for realtime interview questions and 9739782164 this is my no srpsrp777@gmail.Com please guys please provide your no for contact i have a doubts i want to clarify
In a Standard form a DFF is disable?How we can Enable it?
How to link between inventory and OrderManagement.
why we can use synonym?
in procedure how to return a value
How to create user and how u attach with responsibility.
If the flat file is having the duplicate records in the interface how can you restrict while transferring the flat file data in to interface tables?
What is the Exception. Types of exception, Difference between those. And example
In one flat file i got headers and lines data how you can write control file now ?