0

I Written a Query to Identify the Duplicate records.

As Below.

WITH DUPS AS
  (SELECT A_SURVEYID,
    CAST(e_responsedate AS DATE) AS e_responsedate,
    E_LG_VM_SURVEY_TYPE_ENUM
  FROM TRANSIENT..INTERIM_NPS_SURVEY_MOBILE_RESULTS_20170909 a
  GROUP BY A_SURVEYID,
    CAST(e_responsedate AS DATE),
    E_LG_VM_SURVEY_TYPE_ENUM
 HAVING COUNT(*) > 1
  ),
  RANKED AS
  (SELECT R.DRS_RECORD_ID,
    R.A_SURVEYID,
    R.e_responsedate ,
    ROW_NUMBER() OVER ( PARTITION BY R.A_SURVEYID, CAST(R.e_responsedate AS DATE),
       R.E_LG_VM_SURVEY_TYPE_ENUM ORDER BY 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 ) AS DR
  FROM TRANSIENT..INTERIM_NPS_SURVEY_MOBILE_RESULTS_20170909 R
  INNER JOIN DUPS
  ON R.A_SURVEYID                    = DUPS.A_SURVEYID
  AND CAST(R.e_responsedate AS DATE) = DUPS.e_responsedate
  AND R.E_LG_VM_SURVEY_TYPE_ENUM     = DUPS.E_LG_VM_SURVEY_TYPE_ENUM
  )
 SELECT *
FROM TRANSIENT..INTERIM_NPS_SURVEY_MOBILE_RESULTS_20170909 F
INNER JOIN RANKED
ON F.DRS_RECORD_ID = RANKED.DRS_RECORD_ID
WHERE RANKED.DR     > 1

--

By Using the Above Query am able to retrieve the records.(some 6000+ ).

But am unable to delete those records.

Can you please help me on this.

Regards,

Krish

Krish
  • 1

1 Answers1

0

You are very close. In the last 5 lines, do this instead:

Delete from FROM TRANSIENT..INTERIM_NPS_SURVEY_MOBILE_RESULTS_2017090
Where DRS_RECORD_ID in (
    Select DRS_RECORD_ID from RANKED WHERE DR>1)

Should work :)

BTW: I'm pretty sure this can be done with less code, but that is not important...

Lars G Olsen
  • 1,093
  • 8
  • 11
  • I tried with the same by adding delete in the qurey but am getting [42000] error in netezza.. – Krish Sep 12 '17 at 02:54
  • And you did as I wrote ? Deleting with JOIN syntax is slightly more difficult as I remember it. And what is the text accompanying '42000' I'm can't remember all error codes by heart :) – Lars G Olsen Sep 12 '17 at 17:11