Loading

How to delete child records from tradecode that not have par

How to delete child records from tradecode that not have par

ahmed elbarbary

6y733

problem

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

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
(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')
CREATE TABLE #TradeCode
(
TradeCodeId int identity(1,1),
PartId int,
CodeType nvarchar(50),
CodeValue nvarchar(50)
)
insert into #TradeCode(PartId,CodeType,CodeValue)VALUES
(1222,'ECCS-US','AB123-US'),
(1255,'ECCS-US','AB555-US'),
(1222,'ECCS-URB','AB123-URB'),
(1255,'ECCS-URB','AB555-URB'),
(1444,'ECCS-URB','AB666-URB'),
(1931,'ECCS-URB','AB778-URB')

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
can any one help me
what i try is
delete t from #tradecode t
left join  #mappingcodevalue pn on t.CodeValue=pn.ParentCodeValue and t.CodeType=pn.ParentCodeType
where t.CodeValue is null
but not give me correct result