CODEWITHSUNDEEP
sqlsql-serversql-delete
PolarVortex8
PolarVortex8

Reputation: 49

Using a DELETE FROM Clause with Multiple Conditions and Data From Another Table

I have two tables. One is a table with price movements, the other is a table with price movements and an adjusted return.

I want to delete data from the price movement table based on data in the return table.

Price movement table looks like this:

SecurityID TradeDate  OneDayReturn  
3774978    8/20/2020  -0.0013  
3775071    8/20/2020  -0.0034  
3775156    8/20/2020  -0.027  
3776090    8/20/2020  -0.0012

Return table looks like this:

SecurityID  TradeDate  OneDayReturn  TodaysIndexSecurityId  IndexReturn TodaysBeta   AdjReturn  
3774978     8/20/2020  -0.0013       21814                  0.0033      0.246153161  0.000812305  
3775071     8/20/2020  -0.0034       14278                  NULL        0.952705801  NULL  
3775156     8/20/2020  -0.027        21814                  0.0033      1.57887578   0.00521029  
3776090     8/20/2020  -0.0012       6859                   NULL        1.478636146  NULL

I want to delete a row from pricemovements where ABS(AdjReturn * 2) is greater than ABS(OnedayReturn) but also keep the row if there is a sign flip between OneDayReturn and IndexReturn. So, I'm saying only delete it if OneDayReturn*IndexReturn is positive.

I tried this:

DELETE FROM #PriceMovements
WHERE (ISNULL( ABS( SELECT AdjReturn * 2 FROM #AdjReturnTable ), 0 ) > 
ABS(OneDayReturn) AND ISNULL(OneDayReturn,0) * ISNULL(IndexReturn,0) > 0);

I am getting the following error:

Msg 156, Level 15, State 1, Line 129
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 129
Incorrect syntax near ')'.

I assume this means that I'm using AND incorrectly or that I can't reference another table in this statement...but is that why?

Upvotes: 0

Views: 525

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use EXISTS with SecurityID as the common matching column :

DELETE p
  FROM #PriceMovements p
 WHERE EXISTS ( SELECT 0
                  FROM #AdjReturnTable 
                 WHERE ISNULL(ABS (AdjReturn * 2), 0) > ABS(OneDayReturn) 
                   AND ISNULL(OneDayReturn, 0) * ISNULL(IndexReturn, 0) > 0
                   AND SecurityID = p.SecurityID );

In your case, the SELECT Statement has a wrong place as nested within a function.

Upvotes: 1

Related Questions