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 |
In support project one ticket alloted which steps follows manualy tell me ?
A file has both header and corresponding lines. Explain design to load this data into respective interface tables.
explain o2c cycle? what are the table reflected for this process?
Could any one tell me the steps of conversions and what kind of validations and exceptions do we use during this conversion? Please do answer in detail. what program do we write in user exit in reports?
what is po approval hierarchy?
In a table their 20 records.I had update 6 records???How can see and retrieve particular 6 records.i.e.,Latest updated.
10 Answers HCL, TCS,
In one flat file i got headers and lines data how you can write control file now ?
What is ERP? Architecture of apps?
What is REF Cursor?
19 Answers Infosys, Intelligroup,
in procedure how to return a value
Define MultiOrg Structure
what is the difference between request group and request security in oracle apps?