More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
128. How to remove duplicate records from a table?
Answer1
Sol. DELETE FROM table_name WHERE id=1 LIMIT 1;
Answer2
in Oracle:-
delete from table_name
where id = 1
and rowid != (select max(rowid) from table_name where id = 1);
Note - min() can be used in place of max(). max() will keep the latest record and min() will keep the oldest record.
Answer3
in Oracle:-
delete from emp
where rowid not in (select max(rowid) from emp group by empno);
(Continued on next question...)
