Syntax help please .......

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 8, 2015 at 9:22 am

  #305370

  I use this syntax to delete duplicates ( if any ) from a table tmpMHPCLMDET.

  In the list of fields, I have included all fields except 2 ( One of them is an ID int (IDENTITY ) column. As you know this will always have a unique value )

  This runs well and I get the message-->, 0 rows affected

  Why is that ?

  Also when I run a SELECT I see duplicates are present. What am I doing wrong ?

  with basedata2 as (

  select

  *,

  rx = row_number() over

  (partition by

  FILET,ADMDT,FORMN,SSVDT,PCDCD,PRVNO,PCDQT,BILAM,ALWAM,COPAM,WITAM,NETAM,PAYST,

  AJRSN,DCTAM,NCVAM,NCRSN,EDI835RSN,CHKNO,CHKDT,HCPCS,OICPD,REVCD,HLIID,IDAMT,

  APCCD,APPAYST,BANCD,RCVDT,APCSI,ESSNPI,SSNPI,

  IsReversal,PatientResponsibility,Denied,ENSVDT,Mod2,Mod3,Mod4 order by (select null)

  )

  from

  tmpMHPCLMDET

  )

  Delete from basedata2 where rx > 1;

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  October 8, 2015 at 9:28 am

  #1832333

  Please ignore this...

  The above syntax works perfectly.... I looked closely and found a field that had different values.

  So we are good to go...