How to delete from trades table when Part id not exist on mapping table and have count =1 and on code type 3030?
How to delete from table trades where
Part id not exist on mapping table and have count =1 and on code type 3030?
I work on sql server 2012 I face issue i can't delete partid from table trades table where Partid not exist on mapping table and have count =1 and on code type = 3030
so as example partid 2200 not exist on table mapping because code type 8080 and code type 3030 not exist on table mapping and on same time it have count 1 and his code type 3030
so How to write query make delete from table trades where
Part id not exist on mapping table and have count =1 and on code type 3030?
- create table #trades
- (
- TradeCodesId int identity(1,1),
- PartId int,
- CodeTypeId int,
- Code int,
- PartLevel int
- )
- insert into #trades(PartId,CodeTypeId,Code,PartLevel)
- values
- (1348,9090,13456,0),
- (1348,7070,13000,0),
- (1387,9090,13456,0),
- (1387,7070,13000,0),
- (1390,8080,13456,0),
- (1390,3030,19000,0),
- (1800,8080,13570,0),
- (1800,3030,28000,0),
- (2200,3030,74000,0),
- (2500,3030,13570,0),
- (2950,3030,74000,0),
- (2580,3030,13570,0)
-
-
- create table #mapping
- (
- MapId int,
- CodeTypeFrom int,
- CodeTypeTo int,
- CodeValueFrom int,
- CodeValueTo int
- )
- insert into #mapping(MapId,CodeTypeFrom,CodeValueFrom,CodeTypeTo,CodeValueTo)
- values
- (3030,9090,13456,7070,13000),
- (3035,9095,13570,7075,14000)
-
- expected result
-
- TradeCodesId PartId CodeTypeId Code PartLevel
- 9 2200 3030 74000 0
- 10 2500 3030 13570 0
- 11 2950 3030 74000 0
- 12 2580 3030 13570 0