Hi Gurus,
we are planing to Delete the data in small size from the couple of table's in Production.
Million's of data having in the each table.
We dont have the permission through anonymous block in Prod. just to Delete the data with the simple SQL query.
Can someone tell how to convert the below select statement into the DELETE statement and i tried but it's throwing the error and i am stuck here.
--Delete asdf
Select *
From
(
Select t.*, Row_number() Over ( Order by date_created,id ) As rn
FROM t
where date_created < '01-Mar-2016'
) asdf
Where asdf.rn <= 5 ;
Drop table t ;
create table t (id number,
date_created date,
val number) ;
insert into t values (1, to_date('01/01/2016','DD/MM/YYYY'), 50);
insert into t values (1, to_date('01/01/2016','DD/MM/YYYY'), 25);
insert into t values (1, to_date('01/01/2016','DD/MM/YYYY'), 30);
insert into t values (2, to_date('01/02/2016','DD/MM/YYYY'), 10);
insert into t values (2, to_date('01/02/2016','DD/MM/YYYY'), 20);
insert into t values (2, to_date('01/02/2016','DD/MM/YYYY'), 30);
insert into t values (3, to_date('01/03/2016','DD/MM/YYYY'), 40);
insert into t values (3, to_date('01/03/2016','DD/MM/YYYY'), 70);
insert into t values (3, to_date('01/03/2016','DD/MM/YYYY'), 90);
Commit;
--Delete asdf
Select *
From
(
Select t.*, Row_number() Over ( Order by date_created,id ) As rn
FROM t
where date_created < '01-Mar-2016'
) asdf
Where asdf.rn <= 5
;
Thanks in Advance