Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Location
    Syracuse, NY
    Posts
    3

    Unanswered: Finding & Deleting Specific Duplicates

    I have the following select statement to find duplicates, which works for the intended purpose of finding all duplicates for a combination of those fields.
    SELECT LOC_FLD1, LOC_FLD2, LOC_FLD3, LV_APT,LV_AREA,MUN,ST_NUM,TEXT_ID,TEXT_ID2,COUNT(* ) AS DUPS
    FROM sp_ad
    GROUP BY LOC_FLD1,LOC_FLD2,LOC_FLD3,LV_APT,LV_AREA,MUN,ST_N UM,TEXT_ID,TEXT_ID2
    HAVING COUNT (*) > 1; -- 10865 ROWS

    Now I need to extract from the resulting rows only those records that meet a specific criteria in another column (that cannot be included in the previous statement as it messes up the results). The other column is called fcode and the items I need are those that have the fcode of ALARM.

    When I get this worked out I then need to change it to a delete statment, to delete just the rows meeting both of these criteria.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    DELETE sp_ad
    FROM (SELECT LOC_FLD1, LOC_FLD2, LOC_FLD3, LV_APT,LV_AREA,MUN,ST_NUM,TEXT_ID,TEXT_ID2, COUNT(* ) AS DUPS
    		FROM sp_ad
    		GROUP BY LOC_FLD1,LOC_FLD2,LOC_FLD3,LV_APT,LV_AREA,MUN,ST_N UM,TEXT_ID,TEXT_ID2
    		HAVING COUNT (*) > 1) AS T
    WHERE sp_ad.LOC_FLD1 = T.LOC_FLD1 AND 
    		sp_ad.LOC_FLD2 = T.LOC_FLD2 AND
    		sp_ad.LOC_FLD3 = T.LOC_FLD3 AND
    		sp_ad.LV_APT = T.LV_APT AND 
    		sp_ad.LV_AREA = T.LV_AREA AND
    		sp_ad.MUN = T.MUN AND
    		sp_ad.ST_NUM = T.ST_NUM AND
    		sp_ad.TEXT_ID = T.TEXT_ID AND
    		sp_ad.TEXT_ID2 = T.TEXT_ID2 AND
    		sp_ad.fcode = 'ALARM'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Location
    Syracuse, NY
    Posts
    3
    Wim, Thank you for your post but I still cannot get it to work. I wanted to check the data that I was going to delete, before I delete it. With this, I tried modifying your statement, changing the delete to a select. When I try to run this, I get error messages like the one listed below, but for each column that is called out in the statement. Any ideas?

    ERROR MESSAGE:
    The multi-part identifier "sp_ad.LOC_FLD1" could not be bound.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Does the table sp_ad contain a column named LOC_FLD1?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2009
    Location
    Syracuse, NY
    Posts
    3
    Wim, Yes it does contain that field.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you post the DDL of that table?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •