How to delete child records from table tradecode that not have parent on tradecode?


How to delete child records from table tradecode that not have parent on tradecode ?

parent and child exist on table trade code based on table MappingCodeValue parent and child

so i need to delete records from trade code table that not have parent on table trade code

so according to my explain two rows 5,6 on trade code table will be deleted

TradeCodeId  PartId	CodeType   CodeValue	
5	     1444	ECCS-URB    AB666-URB	          
6	     1931	ECCS-URB    AB778-URB

5 and 6 is child and not have parent rows as AB666-US and AB778-US

so it wrong and i will delete it

but another rows on trade code have parent and child according to table mappingcodevalue so that it is correct

so how to write query delete rows that have rows that have child and not have parent from trade code

based on value exist on mappingcodevalue

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

 TradeCodeId int identity(1,1),
 PartId  int,
 CodeType  nvarchar(50),
 CodeValue nvarchar(50)
 insert into #TradeCode(PartId,CodeType,CodeValue)VALUES

I’m not sure what changes need to be made to fit this into your database language, but in general, what I understand you to want to do is something to the tune of:

WHERE CodeValue IN(
    SELECT ChildCodeValue 
    FROM #MappingCodeValue 
    LEFT JOIN #TradeCode 
    ON ParentCodeValue = CodeValue 
    WHERE CodeValue IS NULL

(Untested, spitball code)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.