How to delete records from table trade code that No have parent code value and parent
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