October 25, 2011 at 8:56 am
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
October 25, 2011 at 9:19 am
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)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply