Removing rows from table 1 based on rows in table 2

Question

   Table 1
    Color
    ------
    Red
    Red
    Blue

    Table 2
    Color
    ------
    Red
    Blue

    Result
    Red (since red in Table 1 2x and only 1x in Table 2)

Can you help me develop TSQL to delete rows in table 1 based on the rows in table 2.

In other words, repeat table 2 once and for each color, delete one color from table 1 (not all colors corresponding to the current color in table 2)

Answer 1

Just specify each color and delete everything where the number is greater than 1:

;with cte as(select t1.*, row_number() over(partition by t1.color
                            order by(select null)) as rn
             from table1 t1
             join table2 t2 on t1.color = t2.color)
delete from cte where rn > 1

Or change to:

delete from cte where rn = 1

if you want to remove only one row for each color.

Answer 2

DECLARE @Table1 Table (color nvarchar(4))
INSERT INTO @Table1  Values ('Red')
INSERT INTO @Table1  Values ('Red')
INSERT INTO @Table1  Values ('Blue')

DECLARE @Table2 Table (color nvarchar(4))
INSERT INTO @Table2 Values ('Red')
INSERT INTO @Table2  Values ('Blue')

DELETE a
FROM (
SELECT t1.*,
    row_number() OVER (
        PARTITION BY t1.color ORDER BY (SELECT 1)
        ) AS rownum

FROM @Table1 t1
WHERE t1.color IN (
        SELECT t2.color
        FROM @Table2 t2
        )
) a
WHERE a.rownum =1

change to a.rownum> 1 if you need to remove duplicate color