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)
How can I design the TSQL to delete rows in Table 1 based on the rows in Table 2?
In other words, iterate Table 2 one time and for each color, delete one color from Table 1 (not all the colors that equal current color of Table 2).
Just number each color and delete all where number is greater then 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 delete only one row for each color.
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 delete duplicate color
delete from table1 where color in (select color from table2)this will delete rows which are in table 2..Table2has more than one row with the sameColor? Your sample data doesn't cover many cases, it would help if you added more rows to it.