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

[johnsonmillie53]

You can use a DELETE statement with a subquery. The subquery would be used to select the records that do not have a parent code value and parent, and the DELETE statement would then remove those records from the table.

Example of how the SQL query would look:

DELETE FROM trade
WHERE NOT EXISTS (SELECT 1 FROM trade WHERE parent_code IS NOT NULL AND parent IS NOT NULL);
This query will delete all records from the "trade" table where the parent_code column is NULL and the parent column is NULL.

You can also use this query to delete records where the parent_code and parent value is empty

DELETE FROM trade
WHERE (parent_code ='' OR parent_code IS NULL) AND (parent ='' OR parent IS NULL);