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.

Result should be

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 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 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!

CodePudding user response：

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.