Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Weird Delete / Keeping 1 row

    Based on the included sample data set I would like to only keep those records that are associated with CMT_TYPES = 'OFFICER' *only* if there are multiple rows per CUST_ID. In other words, if a CUST_ID has only 1 row with CMT_TYPES = 'BLEH' I would like to keep it, but if CUST_ID = 5 has 2 rows, one with CMT_TYPES = 'BLEH' and another CMT_TYPES = 'OFFICER', then I only want to keep the latter. I can't get my head around this DELETE statement, so I'm hoping someone can chime in.

    TIA
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28

    Script is Now

    First test this script on the test database or backup database then apply on the production



    DECLARE @REC INT
    DECLARE @CUST_ID INT
    DECLARE @COMM_ID INT
    DECLARE @COUNT INT
    DECLARE _CURSOR CURSOR FOR
    SELECT DISTINCT CUST_ID FROM TEST

    OPEN _CURSOR

    FETCH NEXT FROM _CURSOR INTO @CUST_ID

    WHILE @@FETCH_STATUS =0
    BEGIN
    SELECT @COUNT = COUNT(*) FROM TEST
    WHERE CUST_ID =@CUST_ID

    SELECT TOP 1 @COMM_ID=COMM_ID FROM TEST
    WHERE CUST_ID = @CUST_ID
    ORDER BY CMT_START_DTS DESC

    SET @COUNT = @COUNT - 1


    PRINT '------------------'

    PRINT @COUNT
    PRINT @COMM_ID
    PRINT @CUST_ID

    PRINT '------------------'

    DELETE TOP(@COUNT) FROM TEST
    WHERE CUST_ID = @CUST_ID AND COMM_ID != @COMM_ID

    FETCH NEXT FROM _CURSOR INTO @CUST_ID
    END

    CLOSE _CURSOR
    DEALLOCATE _CURSOR


    SELECT * FROM TEST
    ORDER BY CMT_START_dtS DESC

Tags for this Thread

Posting Permissions

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