If you want to delete duplicate rows but not all rows in table, you can use a procedure. For example, you have 10 duplicate rows and you want to delete 9 rows, then you can use procedure to delete process. For instance, you have a table as tmp_table. tmp_table’s columns are id,name,surname,city,phone. You want to delete duplicate for name,surname,city. You can use below procedure for this process.
Delete Duplicate Rows Procedure
CREATE OR REPLACE PROCEDURE deleteduplicate
IS
BEGIN
FOR duplicates IN ( SELECT max(id) id, name, surname, city
FROM tmp_table GROUP BY name,surname,city
HAVING count(*)>1)LOOP
DELETE FROM tmp_table t
WHERE t.name = duplicates.name
AND t.surname = duplicates.surname
AND t.city = duplicates.city
AND t.id != duplicates.id;
END LOOP;
COMMIT;
END;
to delete only old dates one you can use below script. This script for me :)
delete from tmp_table where id in(
select a.id from tmp_table a,tmp_table b
where a.tarih < b.tarih and a.serino = b.serino and a.serino in (select serino from tmp_table where group by serino having count(1)>1));