How to delete from #partattaributes when count of rows per code and codetype is bigger than code and codetype on #test ?

Question

I work sql server 2012 I face issue i need make delete statement to delete all rows
from #partattributes table when count of rows per code and code type both
is less than count of rows per same code and code type both on table test
as example

count of rows on table #test per code 8538906000 and code type 849774 is 2
count of rows on table #partattaributes per code 8538906000 and code type 849774 is 1
2 is bigger than 1 then delete all code from #partattaributes for code
8538906000 and code type 849774

    create table #test  
(  
codetypeid int,  
code varchar(20),  
zplid int,  
zfeaturekey int  
)  
insert into #test(codetypeid,code,zplid,zfeaturekey)  
values  
(849774,8538906000,4123,160001),  
(849774,8538906000,4123,160003),  
(199987,8538906077,4125,160020),  
(199987,8538906077,4125,160050)  
  
----drop table #partattaributes  
create table #partattaributes  
(  
partid int,  
code varchar(20),  
codetypeid int  
)  
insert into #partattaributes(partid,code,codetypeid)  
values  
(1024,'8538906000',849774),  
(2050,'8538906077',199987),  
(2050,'8538906077',199987)  
  
what i try   




delete p FROM #partattaributes p   
LEFT JOIN #test t on t.code=p.code and t.codetypeid=p.codetypeid  
where t.code is null  

result must delete from table #partattaributes is :

Answer 1

Well, it isn't something that's a single step, you need to identify how many are in each... as a rough idea since I don't have SQL Server to play with

with x as (
  select *, count(*) over (partition by code) as c
  from partattributes
),
y as (
  select *, count(*) over (partition by code) as c
  from test
)
delete x
from x, y 
where x.code = y.code
and x.c < y.c