question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ahmedsalah-1628 commented

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

77380-image.png


sql-server-generalsql-server-transact-sql
image.png (3.3 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can any one help me

0 Votes 0 ·

If you help us to help you, maybe we can help you.

I looked at this, but I was not able to understand how the columns in the #mappings table are related to #trades.

You talk about deleting rows with count = 1. I assume that you mean that since 1348, 1397. 1390 and 1800 appear twice in #trades, they have a count of 2.

But then I get lost when you say that 3030 does not exist in the mapping table. I can very clearly see 3030 in that mapping table. Then again, I don't know which columns you are really mapping to or what those column mean.

And to be honest, this is a general problem with your questions. It's great that you post CREATE TABLE + INSERT statements, but when you don't explain what the columns are good for, this still leaves us in a maze.

0 Votes 0 ·

Hi @ahmedsalah-1628,

Could you please validate all the answers so far and provide any update?

If none of them is working, please provide more sample data and more details about requirement.

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ahmedsalah-1628 commented

Hi @ahmedsalah-1628,

Could you please provide any update or more details?

As suggested by Erland, you could refer below method using Between instead of UNION and check whether it is working.

 select * 
 from #trades
 where partid in (
 select partid from #trades group by partid having count(*)=1) 
 and PartId not in 
 ( select PartId from #trades a ,#mapping b 
 where a.CodeTypeId  between iif(CodeTypeFrom>CodeTypeTo,CodeTypeFrom,CodeTypeTo) and iif(CodeTypeFrom>CodeTypeTo,CodeTypeTo,CodeTypeFrom))
 and CodeTypeId=3030

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you very much solved issue

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @ahmedsalah-1628,

Please also refer below:

 --delete
 select * 
 from #trades
 where partid in (
 select partid from #trades group by partid having count(*)=1) 
 and PartId not in 
 (select PartId from #trades a inner join #mapping b on a.CodeTypeId=b.CodeTypeFrom
 union 
 select PartId from #trades a inner join #mapping b on a.CodeTypeId=b.CodeTypeTo)
 and CodeTypeId=3030

If above is still not working, please provide more sample data and expected output so that we could proceed to enhance this query. Thanks.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Melissa, Ahmed's questions surely are not easy to understand, but shouldn't there be a BETWEEN somewhere rather than a UNION?

Although it is confusing since in the sample data, CodeTypeTo is less than CodeTypeFrom.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

Try this:

 ;WITH CTE_Count_Part AS (
  SELECT PartId, COUNT(*) AS PartCount
  FROM #trades
  GROUP BY PartId
 ),
 CTE_List_Part_In_Mapping AS (
  SELECT t.PartId
  FROM #trades AS t
  INNER JOIN #mapping AS m1 ON m1.CodeTypeFrom = t.CodeTypeId AND m1.CodeValueFrom = t.Code
  INNER JOIN #mapping AS m2 ON m2.CodeTypeTo = t.CodeTypeId AND m2.CodeValueTo = t.Code
 )
    
 SELECT *
 FROM #trades
 WHERE PartId IN (SELECT PartId FROM CTE_Count_Part WHERE PartCount = 1)
 AND  PartId NOT IN (SELECT PartId FROM CTE_List_Part_In_Mapping)
 AND CodeTypeId = 3030;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ahmedsalah-1628,

Please refer below updated one and check whether it is working.

 --delete
 select * 
 from #trades
 where partid in (
 select partid from #trades
 group by partid
 having count(*)=1) 
 and CodeTypeId not in 
 (select CodeTypeFrom from #mapping
 union
 select CodeTypeTo from #mapping)
 and CodeTypeId=3030

Output:

 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

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ahmedsalah-1628 commented

Hi @ahmedsalah-1628,

Could you please provide more details about "Part id not exist on mapping table"?

After checking, all partids are not exists in mapping table.

You could refer below according to "have count =1 and on code type 3030".

  delete
 --select * 
 from #trades
 where partid in (
 select partid from #trades
 group by partid
 having count(*)=1) 
 and CodeTypeId=3030

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you for reply

first condition partid 2200,2500,2950,2580 exist only one time and count =1
second condition partid 2200,2500,2950,2580 not exist on table mapping
because code type 3030 not exist on table mapping
i have on table mapping on code type from or code type to
9090,9095,7070,7075
so 3030 not exist
third condition must code type =3030

so what is confuse on result

0 Votes 0 ·