CODEWITHSUNDEEP
sqlsql-server
Somasekhar Ghanta
Somasekhar Ghanta

Reputation: 81

Delete the records from a table following a rule

Rule: With in the Yearquarter if there are multiple rows of types (3,4) with status='a' and type=1 with status='a' for employerid we need to keep type=1 with status='a' within yearquarter for employerid and the rest should be deleted

This is the table EMP

EMPID    YEARQUARTER   TYPE   STATUS
105        20021        3       A
105        20021        1       A
106        20091        4       A
106        20091        1       A
107        20101        4       A
107        20101        1       B
107        20101        2       A
108        20111        3       A
108        20111        1       A
108        20111        2       B
109        20131        3       A
109        20131        1       A
109        20131        1       A

After this delete the EMP table should be left with :

EMPID    YEARQUARTER   TYPE   STATUS
105        20021        1       A
106        20091        1       A
107        20101        4       A
107        20101        1       B
107        20101        2       A
108        20111        1       A
108        20111        2       B
109        20131        1       A
109        20131        1       A

I'm trying to use this statement, but it doesn't get the desired result.

DELETE e1
FROM
    EMP e1
WHERE 
      EXISTS (
                SELECT 
                    *
                FROM
                    EMP e2
                WHERE 
                        e1.EMPID = e2.EMPID
                    AND e1.yearquarter = e2.yearquarter
                    AND e2.[status] = 'A'
                    AND E2.[type] IN (3,4)
                )

After executing this all my rows on the table are getting deleted.

Upvotes: 1

Views: 41

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6465

You should check that you want to delete Types 3 and 4 on the E1 dataset and not the E2 dataset.

DELETE E1
FROM
    EMP E1
WHERE 
      E1.TYPE IN (3,4)      
  AND E1.STATUS = 'A'
  AND EXISTS (
                SELECT 
                    *
                FROM
                    EMP E2
                WHERE 
                        E2.EMPID = E1.EMPID
                    AND E2.YEARQUARTER = E1.YEARQUARTER
                    AND E2.TYPE = 1
                    AND E2.STATUS = 'A'
               )

Upvotes: 1

Altaf Khokhar
Altaf Khokhar

Reputation: 306

@Somasekhar Ghanta,

The solution for your requirement is 

 DELETE  FROM Yearquarter AS tbl WHERE tbl.TYPE IN(3,4) AND tbl.Status = 'a'

Also note that your OUTPUT HAS 1 ROW with type 4 and status A: 107 20101 4 A

Also take care of lower and upper case of status.

Upvotes: 0

Related Questions