# Database Discussions > Microsoft SQL Server 2008 >  How to delete records from table trade code that No have parent code value and parent

## ahmedsa

I work on SQL server 2012 I need to delete rows that not have parent code type and parent code value


```
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 



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


```
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
```

----------


## skhanal

I do not understand the requirement when you say -  rows that not have parent code type and parent code value

For the sample you posted all records have parent code type and value

----------

