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

ahmed salah 3,216 Reputation points
2021-08-08T22:49:48.343+00:00

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 :

121388-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ryan Abbey 1,176 Reputation points
    2021-08-09T00:15:50.28+00:00

    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
    
    0 comments No comments

0 additional answers

Sort by: Most helpful