sql server - Delete rows from Table 1 based on the rows in Table 2 -

   table 1     color     ------     red     red     blue      table 2     color     ------     red     blue      result     red (since red in table 1 2x , 1x in table 2) 

can please me design tsql delete rows in table 1 based on rows in table 2.

in other words, iterate table 2 1 time , each color, delete 1 color table 1 (not colors equal current color of table 2)

just number each color , delete number greater 1:

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

or change to:

delete cte rn = 1 

if want delete 1 row each color.