I work on SQL server 2012 I need to delete rows that not have parent code type and parent code value
Code:
drop table #MappingCodeValue
drop table #TradeCode
create table #MappingCodeValue
 (
 id int identity (1,1),
 ParentCodeType  nvarchar(50),
 ParentCodeValue  nvarchar(50),
 ChildCodeType  nvarchar(50),
 ChildCodeValue  nvarchar(50)
 )
 INSERT INTO #MappingCodeValue
 (ParentCodeType,ParentCodeValue,ChildCodeType,ChildCodeValue)
 VALUES
 ('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),
 ('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),
 ('ECCS-US','AB666-US','ECCS-URB','AB666-URB'),
 ('ECCS-US','AB778-US','ECCS-URB','AB778-URB'),
 ('HTS-US','AB900-US','SCHEDUALB','AB900-URB')

 --select * from #MappingCodeValue
 CREATE TABLE #TradeCode
 (
 TradeCodeId int identity(1,1),
 PartId  int,
 CodeType  nvarchar(50),
 CodeValue nvarchar(50),
 PartDone  bit
 )
 insert into #TradeCode(PartId,CodeType,CodeValue,PartDone)VALUES
 (1222,'ECCS-US','AB123-US',null),
 (1255,'ECCS-US','AB555-US',null),
 (1222,'ECCS-URB','AB123-URB',1),
 (1255,'ECCS-URB','AB555-URB',1),
 (1444,'ECCS-URB','AB666-URB',1),
 (1931,'ECCS-URB','AB778-URB',1),
 (7000,'ECCS-URB','AB778-URB',1),
 (8000,'ECCS-URB','AB778-URB',1),
 (800,'SCHEDUALB','AB900-URB',1),
 (900,'SCHEDUALB','AB900-URB',1),
 (1255,'HTS-US','AB900-US',null),
 (909,'HTS-US','AB900-US',null)
what i try is

Code:
;with mycte as (
 select Partid from #TradeCode t where PartDone=1
 group by Partid 
having (count(*)=1)
)

delete t 
from  #TradeCode t 
join mycte m on m.Partid=t.Partid


select * from #TradeCode
Expected result after delete
Code:
TradeCodeId    PartId	CodeType	CodeValue	PartDone
1	        1222	ECCS-US  	 AB123-US	   null
2	        1255	ECCS-US	         AB555-US	   null
3	        1222	ECCS-URB	 AB123-URB   	   1
4	        1255	ECCS-URB	 AB555-URB	   1
11	        1255	HTS-US	         AB900-US	   null
12	        909	HTS-US	         AB900-US	   null