I work on SQL server 2012 I need to delete rows that not have parent code type and parent code value from table Trade 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)
3 and 4 not deleted because every part have code type from and code value from is mapping
and is correct
so i will not delete
it is generated from this lines
('ECCS-US','AB123-US','ECCS-URB','AB123-URB'),
('ECCS-US','AB555-US','ECCS-URB','AB555-URB'),
so part have two side from and to so i will not delete
i will delete parts that have from and have to
so it is not have parent to give me result
but records or parts have parent and also child will not delete
and this apply on 3 and 4
Expected Result after delete is
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
File sharing and storage made simple[
^]
What I have tried:
;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