How to delete from #partattaributes when count of rows per code and codetype is

Question

Post reply

How to delete from #partattaributes when count of rows per code and codetype is

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

August 9, 2021 at 12:06 am

#3917978

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 :

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12542 More actions · Answer 1

Build two lists: the first list is all of the #partattributes code and code types with counts, the second is a list of #test code and code types with counts. Compare the two lists and act on the results:

--drop table #test;
--drop table #partattaributes;

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);
    
 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);

-- Before delete
SELECT 'Before', * FROM #partattaributes;

WITH 
-- list #test values and counts
TestParts(codetypeid, code, TestTypeAndCodeCount) AS
( SELECT codetypeid, code, count(*) AS [TestTypeAndCodeCount]
    FROM #test
   GROUP BY codetypeid, code),
-- list #partattributes and counts
PartAttrs(codetypeid, code, PartTypeAndCodeCount) AS
( SELECT codetypeid, code, count(*) AS [PartTypeAndCodeCount]
    FROM #partattaributes
   GROUP BY codetypeid, code),
-- Combine the lists
CountCompare(codetypeid, code, TestTypeAndCodeCount, PartTypeAndCodeCount, ShouldDelete) AS
( SELECT pa.codetypeID, pa.code, TestTypeAndCodeCount, PartTypeAndCodeCount,
         CASE WHEN PartTypeAndCodeCount < TestTypeAndCodeCount THEN 1 ELSE 0 END AS [ShouldDelete]
    FROM TestParts tp INNER JOIN
         PartAttrs pa ON tp.codetypeid = pa.codetypeid AND tp.code = pa.code)
-- This SELECT statement shows the results of combining the values. 
-- Comment SELECT statement out and un-comment the DELETE statement to perform the delete.
SELECT cc.codetypeid, cc.code, cc.TestTypeAndCodeCount, cc.PartTypeAndCodeCount, cc.ShouldDelete
  FROM CountCompare cc;

-- The DELETE statement:
--DELETE pa
--  FROM #partattaributes pa
-- WHERE EXISTS(SELECT * FROM CountCompare cc 
--               WHERE cc.codetypeid = pa.codetypeid AND cc.code = pa.code
--                 AND cc.ShouldDelete = 1);

-- After delete
SELECT 'After', * FROM #partattaributes;

Eddie Wuerch
MCM: SQL

kaj SSCommitted Points: 1616 More actions · Answer 2

Maybe something like this?

with 
    tcount as (
    select codetypeid,code, count(*) as trows
    from #test
    group by codetypeid,code),

    pacount as (
    select codetypeid, code, count(*) as parows
    from #partattaributes
    group by codetypeid, code)

 delete p 
 FROM #partattaributes p 
 LEFT JOIN tcount tc on tc.code=p.code and tc.codetypeid=p.codetypeid
 LEFT JOIN pacount pc on pc.code=p.code and pc.codetypeid=p.codetypeid
 where isnull(pc.parows,0) < isnull(tc.trows,0)