HOW TO DELETE RECORDS

  • hi,

    My Table contains the following structure:

    create table Delete_Dup_Records

    (

    vid int,

    vName varchar(50),

    vAddress varchar(40),

    VGroup   char(1) default 'N' not null

    )

    It Contains the following Records:

    VID     VNAME     VADDRESS    VGROUP

    1        A              A               N

    1        A              A               N

    1        A              A               N

    1        A              A               N

    2        B              C               N

    2        B              C               N

    3        D              C               N

    4        D              F               N

    5        D              K               N

    5        D              K               N

    I want to delete only that records which are duplicate but there should at least one record remains in the table and output should be the following :

    VID     VNAME     VADDRESS    VGROUP

    1        A              A               N

    2        B              C               N

    3        D              C               N

    4        D              F               N

    5        D              K               N

    To achive this result i want to use simple select query.

    i can't use temp_tables or identity fuctions for this.

    Help me to achieve this

     

     

  • First...why can't you use temp_tables or identify functions?  They are the natural solution.  Create a temp table with the same structure, pass a distinct listing of the records to the temp table, delete all the records in your actual table, then copy back the records from the temp table.

     

    If the phone doesn't ring...It's me.

  • If you don't care using a loop ... this will do

    DECLARE @affected INT

    SET @affected = 1

    WHILE @affected > 0

     BEGIN

     SET ROWCOUNT 1

     DELETE FROM Delete_Dup_Records

     FROM Delete_Dup_Records D

     INNER JOIN (SELECT vid, vName,vAddress,VGroup

        FROM Delete_Dup_Records D2

        GROUP BY vid, vName,vAddress,VGroup

        HAVING COUNT(*) > 1) D3

       ON   D.vid = D3.vid

        AND D.vname = D3.vname

        AND D.vAddress = D3.vAddress

        AND D.vGroup = D3.vGroup

     SET @affected = @@ROWCOUNT

     END


    _/_/_/ paramind _/_/_/

  • declare @Delete_Dup_Records table

    (

    id_val INT IDENTITY(1,1),

    vid int,

    vName varchar(50),

    vAddress varchar(40),

    VGroup   char(1) default 'N' not null

    )

    INSERT INTO @Delete_Dup_Records (VID, VNAME, VADDRESS, VGROUP) SELECT VID, VNAME, VADDRESS, VGROUP FROM Delete_Dup_Records

    PRINT '**************'

    PRINT 'BEFORE DELETE'

    PRINT '**************'

    SELECT VID, VNAME, VADDRESS, VGROUP FROM @Delete_Dup_Records

    DELETE @Delete_Dup_Records

    FROM @Delete_Dup_Records OV

    WHERE EXISTS (SELECT 1 FROM @Delete_Dup_Records IV

       WHERE OV.VNAME = IV.VNAME

       AND OV.VADDRESS = IV.VADDRESS

       AND OV.VGROUP = IV.VGROUP

       AND OV.VID = IV.VID

       AND OV.ID_VAL > IV.ID_VAL)

    PRINT '**************'

    PRINT 'AFTER DELETE'

    PRINT '**************'

    SELECT VID, VNAME, VADDRESS, VGROUP FROM @Delete_Dup_Records

    --Output:

    **************

    BEFORE DELETE

    **************

    VID         VNAME                                              VADDRESS                                 VGROUP

    ----------- -------------------------------------------------- ---------------------------------------- ------

    1           A                                                  A                                        N

    1           A                                                  A                                        N

    1           A                                                  A                                        N

    1           A                                                  A                                        N

    2           B                                                  C                                        N

    2           B                                                  C                                        N

    3           D                                                  C                                        N

    4           D                                                  F                                        N

    5           D                                                  K                                        N

    5           D                                                  K                                        N

    **************

    AFTER DELETE

    **************

    VID         VNAME                                              VADDRESS                                 VGROUP

    ----------- -------------------------------------------------- ---------------------------------------- ------

    1           A                                                  A                                        N

    2           B                                                  C                                        N

    3           D                                                  C                                        N

    4           D                                                  F                                        N

    5           D                                                  K                                        N

     

  • or

    INSERT INTO Delete_Dup_Records

    SELECT

     D2.vid,

     D2.vName,

     D2.vAddress,

     'tricky' + D2.vGroup ,

    FROM Delete_Dup_Records D2

    GROUP BY

     D2.vid,

     D2.vName,

     D2.vAddress,

     D2.vGroup

    DELETE FROM Delete_Dup_Records WHERE vGroupName NOT LIKE 'tricky%'

    UPDATE Delete_Dup_Records SET vGroupName = SUBSTRING(vGroupName,7,8000)

    why not complicate it the "tricky" way


    _/_/_/ paramind _/_/_/

  • Yaar,

    You are simply supereb.

    Great !!

     

    Regards

    Amit

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply