Delete row with a complex if

Question

I want to write a query where it deletes duplicates from table where the Access column value is 1 - 5, but always take the highest number but if it's 5 then that should be the lowest, because of how they designed the database. but 5 have no access.(5 should have been 0 in my opinion.)

So there is an ID column and an Access column, if there are more than 1 IDs, then delete the ID with the lowest Access value. But remember treat 5 as 0 or lowest.

So I thought something like:

Delete from [table]
Where [ID] > 1
AND [Access] = (CASE [Access] 
    WHEN Access = 1 THEN ____ <----'Do nothing')
    ...
    WHEN Access = 5 THEN ____ <----'Do Delete')

That is where I struggle. How would I check ID, and see which Access is the highest and delete all the lowest if they exist. Remember, if it's 5 then 4 is actually higher so delete 5.

So confusing!

Answer 1

This should be the fastest and most portable solution as it allows an index to be used on Access and uses standard SQL.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([ID] int, [Access] int)
;

INSERT INTO Table1
    ([ID], [Access])
VALUES
    (1, 5),
    (1, 1),
    (1, 2),
    (2, 5),
    (2, 1),
    (2, 2),
    (3, 5)
;

Query 1:

delete t
from Table1 t
inner join (
    select ID, max(Access) as MaxAccess
    from Table1
    where Access <> 5
    group by ID
) pm on t.Access <> pm.MaxAccess
    and t.ID = pm.ID

select * from Table1

Results:

| ID | ACCESS |
|----|--------|
|  1 |      2 |
|  2 |      2 |
|  3 |      5 |

Answer 2

If you apply modulo 5 to the Access column, you will get the following transformation:

1 % 5 = 1
2 % 5 = 2
3 % 5 = 3
4 % 5 = 4
5 % 5 = 0

As you can see, 5 yields the lowest result, others remain unchanged – seems the proper ranking of privileges for your case. With that in mind, I would probably try the following method:

DELETE FROM u
FROM dbo.UserTable AS u
LEFT JOIN (
  SELECT ID, MAX(Access % 5) AS Access
  FROM dbo.UserTable
  GROUP BY ID
) AS keep
  ON u.ID = keep.ID
 AND u.Access % 5 = keep.Access
WHERE i.ID IS NULL
;

The subquery returns a set of IDs with Access values to keep. The main query anti-joins the target table to that set to determine which rows to delete.

This method is somewhat specific to your particular situation: it may not work correctly after extending the current set of valid values for Access. As an alternative, you could use CASE, as others suggested, that would certainly be more flexible. However, I would actually suggest you add an AccessRank column to the Access table to indicate which privilege is higher or lower than others.

That would make your DELETE query more complex but you wouldn't need to adapt it every time new Access values are introduced (you'd just need to define proper ranking in your data):

DELETE FROM u
FROM dbo.UserTable AS u
INNER JOIN dbo.Access AS a
  ON u.Access = a.ID
LEFT JOIN (
  SELECT u.ID, MAX(a.AccessRank) AS AccessRank
  FROM dbo.UserTable AS u
  INNER JOIN dbo.Access AS a
    ON u.Access = a.ID
) AS keep
  ON u.ID = keep.ID
 AND a.AccessRank = keep.AccessRank
;

It is implied that AccessRank contains unique rankings only.

Answer 3

Sample Access table:

|id|access|
-----------
|1 |  1   |
|1 |  2   |
|1 |  5   |
|2 |  1   |
|2 |  3   | 

On running the below query:

DELETE a 
FROM acc a
INNER JOIN 
(SELECT row= ROW_NUMBER() OVER(PARTITION BY ID ORDER BY  (CASE WHEN ACCESS=5 THEN 0 ELSE ACCESS  END) DESC), * FROM acc) b
ON a.id = b.id AND a.access = b.access 
WHERE b.row > 1

The result is:

|id|access|
|1 |  2   |
|2 |  3   | 

Answer 4

If you want to delete the row for each id that first has access 5 and the rest ordered, then do:

with todelete as (
      select t.*, row_number() over (partition by id
                                     order by (case when access = 5 then -1 else access end)
                                    ) as seqnum
      from table t
     )
delete from todelete
    where seqnum = 1;

If you only want to do this when there is more than one record for the id:

with todelete as (
      select t.*, row_number() over (partition by id
                                     order by (case when access = 5 then -1 else access end)
                                    ) as seqnum,
             count(*) over (partition by id) as cnt
      from table t
     )
delete from todelete
    where seqnum = 1 and cnt > 1;

EDIT:

If you want to delete everything except one row according to your precedence rules:

with todelete as (
      select t.*, row_number() over (partition by id
                                     order by (case when access = 5 then -1 else access end)
                                    ) as seqnum,
             count(*) over (partition by id) as cnt
      from table t
     )
delete from todelete
    where seqnum < cnt;