Need to delete a row from the source table

• hbanerje

  SSC Eights!

  Points: 818

  More actions

  October 25, 2011 at 8:56 am

  #259165

  I have a source table and another table with duplicate rows in it. I want to delete a row from the source table.

  I have written this query but deleting every thing from the source table.

  DELETE from DRG_DATA (source table)

  FROM #All_duplicatedRecord (has two duplicate rows for the same record) AS drg

  LEFT JOIN #All_duplicatedRecord AS dup

  ON drg.enc_num = dup.enc_num

  and dup.QUERY_DT_TM = (SELECT MAX(QUERY_DT_TM) FROM #All_duplicatedRecord)

  I tried the following query too, then wanted to delete the rows where Dup_Rec =1 , but all the records under Dup_Rec showing 1

  update DRG_DATA

  SET Dup_Rec = 1

  from #All_duplicatedRecord A

  inner join

  (select ENC_NUM as enc_num1, PHYSICIAN_LAME as PHYSICIAN_LAME1, RESP_CODE as RESP_CODE1,QUERY_COMM as QUERY_COMM1

  from dbo.[Winston CYQ11 Q2 092811]

  group by ENC_NUM, PHYSICIAN_LAME, RESP_CODE,QUERY_COMM

  having COUNT(*) >1)B

  on

  A.ENC_NUM = B.ENC_NUM1

  and A.PHYSICIAN_LAME =B.PHYSICIAN_LAME1

  and A.QUERY_COMM = B.QUERY_COMM1

  any help will be appreciated.

  Thanks,

  Haimanti

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728164

  More actions

  October 25, 2011 at 9:19 am

  #1399169

  In your first query, you aren't joining to the table you are deleting from.

  What you should do is write a SELECT query first to find out what will be found. This is what you have.:

  select *

  from DRG_DATA d, #All_duplicatedRecord AS drg

  LEFT JOIN #All_duplicatedRecord AS dup

  ON drg.enc_num = dup.enc_num

  and dup.QUERY_DT_TM = (SELECT MAX(QUERY_DT_TM) FROM #All_duplicatedRecord)

  What I think you want is:

  select enc_num

  from DRG_DATA d

  inner JOIN #All_duplicatedRecord AS dup

  ON d.enc_num = dup.enc_num

  and dup.QUERY_DT_TM = (SELECT MAX(QUERY_DT_TM)

  FROM #All_duplicatedRecord)

  Then you convert this to a DELETE with

  delete d

  from DRG_DATA d

  inner JOIN #All_duplicatedRecord AS dup

  ON d.enc_num = dup.enc_num

  and dup.QUERY_DT_TM = (SELECT MAX(QUERY_DT_TM)

  FROM #All_duplicatedRecord)

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com