How to make rows from two table "Cancel" each other by columns values(SQL server)

Question

Say I have A,B two tables, both contains date and time column, I want A table to look at B table, Table A will DELETE 1 rows from itself for every duplicate found in B table.

A
2021-12-24	9:30:00
2021-12-24	10:00:00
2021-12-24	10:30:00
2021-12-24	11:00:00
2021-12-24	11:30:00
2021-12-24	11:00:00
2021-12-24	11:30:00

B
2021-12-24	9:30:00
2021-12-24	11:00:00
2021-12-24	11:00:00
2021-12-24	11:30:00

Result should be

A(Result)
2021-12-24	10:00:00
2021-12-24	10:30:00
2021-12-24	11:30:00

Following are actual code, the @tmp_leave is table A, the @tmp_cancelLeave is table B

I have tried...

Using DELETE FROM WHERE EXISTS (subquery) It removes multiple rows from table A if it find even only 1 duplicate in table B, which I don't want to.

DELETE FROM @tmp_leave 
    WHERE EXISTS (
        SELECT * 
        FROM @tmp_cancelLeave c
        WHERE (SELECT TOP 1 applicant_name FROM @tmp_leave)= c.applicant_name 
    AND (SELECT TOP 1 calender_date FROM @tmp_leave)= c.calender_date
    AND (SELECT TOP 1 timeslot FROM @tmp_leave)= c.timeslot  
)

Using While Loop (It works,but terrible performance and feel stupid ... also if the @tmp_cancelLeave have different order then @tmp_leave, it won't work at all.)

DECLARE @cnt INT = 0;
DECLARE @max INT = (SELECT COUNT(*) FROM @tmp_leave)

WHILE @cnt < @max
BEGIN
DELETE TOP(1) FROM @tmp_leave 
    WHERE applicant_name IN(SELECT applicant_name FROM @tmp_cancelLeave)
    AND calender_date IN(SELECT calender_date FROM @tmp_cancelLeave)
    AND timeslot IN(SELECT timeslot FROM @tmp_cancelLeave)
    
DELETE TOP(1) FROm @tmp_cancelLeave
    
    SET @cnt = @cnt + 1;

Please note that it is not the same as simply using DELETE FROM WHERE EXISTS, since EXISTS will remove multiple rows from table A if it find even only one duplicate in table B, which I don't want to. I just want to delete one identical rows from A for every same rows found in B.

I have been trying this for a day or so, any help is very appreciated!

You want to "remove" the rows, but not `DELETE` them? Seems a `NOT EXISTS` is still what you want, but in a `SELECT` not a `DELETE` statement then. — Thom A, Jun 14 '22 at 08:56
Hi Larnu, thank you for replying, I changed my word to DELETE, thank you for pointing this out. — Cheese, Jun 14 '22 at 09:02
Because DELETE with EXISTS will delete multiple records, I just want to delete one identical rows from A for every same rows found in B. — Cheese, Jun 14 '22 at 09:10
*"Because DELETE with EXISTS will delete multiple records"* Your question clearly shows you *want* to delete multiple **rows** (4 of them). So, again, I don't see why a `DELETE` with an `EXISTS` is not what you want; have you actually tried it? — Thom A, Jun 14 '22 at 09:14

score 2 · Accepted Answer · answered Jun 14 '22 at 09:20

You can do it by assigning a row number to each of your two tables, then deleting from the A table where both date, time and row number match.

WITH cteA AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [date], [time] ORDER BY [time]) AS rn
    FROM A
), cteB AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [date], [time] ORDER BY [time]) AS rn
    FROM B
)
DELETE cteA
FROM       cteA
INNER JOIN cteB
        ON cteA.[date] = cteB.[date]
       AND cteA.[time] = cteB.[time]
       AND cteA.rn     = cteB.rn;

Check the demo here.

This is what I am looking for! Man you are my messiah! – Cheese Jun 14 '22 at 09:40 — Cheese, Jun 14 '22 at 09:40

How to make rows from two table "Cancel" each other by columns values(SQL server)

1 Answers1