Deleting duplicates

Results 1 to 2 of 2

Thread: Deleting duplicates

  1. #1
    Join Date
    Dec 1969

    Default Deleting duplicates

    I am having trouble weeding duplicate records from a table. The table structure is basically:<BR><BR>recID int<BR>storyID int<BR>storyName varchar(50)<BR>storyDate smalldatetime<BR>storyTime smalldatetime<BR>storyText text<BR><BR>I&#039;d like to delete all duplicate records of storyID and StoryName (no matter what the values in the other fields are, because they will be varied even if those two are the same) leaving only one of them.<BR><BR>Thoughts? Ideas?<BR><BR>= )<BR><BR>--C. A.

  2. #2
    Join Date
    Dec 1969

    Default RE: Deleting duplicates

    If recID is a unique integer then this can be done without too much trouble.<BR><BR>DELETE FROM A<BR>FROM Table A<BR>INNER JOIN (<BR> SELECT MAX(recID) AS recID_KEEP, storyID, storyName<BR> FROM Table<BR> GROUP BY storyID, storyName<BR> HAVING COUNT(*) &#062; 1<BR>) AS SQ<BR>ON A.storyID = SQ.storyID AND A.storyName = SQ.storyName<BR>WHERE A.recID &#060; SQ.recID_KEEP<BR><BR><BR>Gavin

Posting Permissions

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