SQL Server keep only one-CodePudding

I am using SQL Server 2008R2 and have a table (TableA) that keeps the conflicts between hotel Functions on certain Dates, Time periods and Rooms.

The problem is that it displays them twice, meaning finds that FunctionA conflicts in some ways with FunctionB and then it displays FunctionB conflicting in the same way with FunctionA

orig_id conflicting_id  event_name  conflict_name   on_date     room_id time_from   time_to conflict_from   conflict_to
22108   22255           FunctionA   FunctionB       15/06/2022  9       07:00       09:30   07:00           09:30
22108   22255           FunctionA   FunctionB       15/06/2022  9       12:30       13:30   12:30           13:30
22108   22255           FunctionA   FunctionB       15/06/2022  30      09:00       17:30   09:00           17:30
22108   22255           FunctionA   FunctionB       15/06/2022  31      09:00       17:30   09:00           17:30
22108   22255           FunctionA   FunctionB       15/06/2022  32      09:00       17:30   09:00           17:30
22255   22108           FunctionB   FunctionA       15/06/2022  9       07:00       09:30   07:00           09:30
22255   22108           FunctionB   FunctionA       15/06/2022  9       12:30       13:30   12:30           13:30
22255   22108           FunctionB   FunctionA       15/06/2022  30      09:00       17:30   09:00           17:30
22255   22108           FunctionB   FunctionA       15/06/2022  31      09:00       17:30   09:00           17:30
22255   22108           FunctionB   FunctionA       15/06/2022  32      09:00       17:30   09:00           17:30

In the case above FunctionA conflicts with FunctionB on on_date,room_id,conflict_from,conflict_to and FunctionB conflicts with FunctionA on the same criteria.

How could I keep just one of the conflict sets?

Thank you

CodePudding user response：

You want to delete rows for which exists a duplicate. So use EXISTS looking for rows with the same criteria, but switched event IDs or names. At last, in order not to remove both rows, decide for one, e.g. the one where the orig_id is greater than the conflicting_id (thus keeping the row where it is vice versa).

delete from conflicts
where exists
(
  select null
  from conflicts same
  where -- same criteria
        same.on_date = conflicts.on_date
    and same.room_id = conflicts.room_id
    and same.time_from = conflicts.time_from
    and same.time_to = conflicts.time_to
    -- but switched IDs
    and same.orig_id = conflicts.conflicting_id
    and same.conflicting_id = conflicts.orig_id
)
and orig_id > conflicting_id;