CODEWITHSUNDEEP
sqlsql-server-2008
Mntne Dav
Mntne Dav

Reputation: 93

Delete script SQL Server 2008

Here is a situation that I am trying to resolve. I have a table that is loaded with duplicates. It happened because similar rows were loaded from two different sources. That is being taken care of in the package.

But I want to delete those rows which are duplicates. There is no key attribute here (I can't use no PK). The two sources are Cleveland City and Ohio State. And I have a column that shows from which source the row was loaded from (DataSource column).

Thus in the row it shows as DataSource = 'Cleveland' or DataSource = 'OhioState'.

Below is a sample which I am stuck with. Could you guys have a different approach to delete those rows mine doesn't seem to be working well? Thanks again guys .. I don't think the way I am trying to approach is even correct...

    IF OBJECT_ID('tempdb..#Ohio') IS NOT NULL 
     BEGIN 
     DROP TABLE #Ohio
     END 

  ;WITH Oh AS 
    ( SELECT ROW_NUMBER()OVER
      (
        PARTITION by UID,ADDRESS,CITY,STATE,Zip
          ORDER BY 
            UID
       ) AS IA,UID,ADDRESS,City,State,Zip FROM F_staRes
     ) 
    SELECT * INTO #Ohio   FROM Oh WHERE IA> 1   AND  DataSource='Ohio'  

    IF OBJECT_ID('tempdb..#Clevland') IS NOT NULL 
    BEGIN 
   DROP TABLE #Clevland
   END 

    ;WITH Cle AS 
     ( SELECT ROW_NUMBER()OVER
      (
           PARTITION by UID,ADDRESS,CITY,STATE,Zip
       ORDER BY 
        UID
         ) AS CE,UID,ADDRESS,City,State,Zip FROM F_staRes
         ) 
   SELECT * INTO #Clevland   FROM Cle WHERE CE> 1  AND  DataSource!='Ohio' 

    select * from #Clevland--I want to delete this records
     Intersect 
     select * from #Ohio

Upvotes: 1

Views: 508

Answers (2)

Erxin
Erxin

Reputation: 1856

  1. I think the key word INTERSECT isn't used properly. The explanation is below. You could follow the link to get detail.

EXCEPT returns any distinct values from the left query that are not also found on the right query. INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand

http://msdn.microsoft.com/zh-cn/library/ms188055.aspx

  1. To achieve your perpose, you could try the command merge.

    ;
merge into #Clevland as target
using  #Ohio as source
on (target.UID = source.UID) -- you could add ADDRESS,City,State,Zip
when not matched
   insert into target (UID) values (source.UID)
;

Wish this will help.

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Maybe it will help you

;WITH cte AS
 (
  SELECT ROW_NUMBER() OVER (PARTITION BY UID, ADDRESS, City, State, Zip, DataSource ORDER BY UID) AS Dup
  FROM dbo.F_staRes
  )
DELETE cte
WHERE Dup > 1

Upvotes: 0

Related Questions