oracle - How to Delete these Records in Netezza(Aginity) -
i written query identify duplicate records.
as below.
with dups (select a_surveyid, cast(e_responsedate date) e_responsedate, e_lg_vm_survey_type_enum transient..interim_nps_survey_mobile_results_20170909 group a_surveyid, cast(e_responsedate date), e_lg_vm_survey_type_enum having count(*) > 1 ), ranked (select r.drs_record_id, r.a_surveyid, r.e_responsedate , row_number() on ( partition r.a_surveyid, cast(r.e_responsedate date), r.e_lg_vm_survey_type_enum order substr(r.drs_record_id, instr(':', r.drs_record_id, 37) + 1, 14) desc, substr(r.drs_record_id, instr(':', r.drs_record_id, 32) + 1, 4) asc ) dr transient..interim_nps_survey_mobile_results_20170909 r inner join dups on r.a_surveyid = dups.a_surveyid , cast(r.e_responsedate date) = dups.e_responsedate , r.e_lg_vm_survey_type_enum = dups.e_lg_vm_survey_type_enum ) select * transient..interim_nps_survey_mobile_results_20170909 f inner join ranked on f.drs_record_id = ranked.drs_record_id ranked.dr > 1
--
by using above query able retrieve records.(some 6000+ ).
but unable delete records.
can please me on this.
regards,
krish
you close. in last 5 lines, instead:
delete from transient..interim_nps_survey_mobile_results_2017090 drs_record_id in ( select drs_record_id ranked dr>1)
should work :)
btw: i'm pretty sure can done less code, not important...
Comments
Post a Comment