how to delete from table based on query results-CodePudding

I have the following query which spits out some values and I need to basically modify it so that I can delete some rows from the source table based on whether the Ticker symbol and date are represented in the output of the select query. Here is the query.

 ;with cte_tenPct as  (
 select exchange, ticker
    , CurrentDate = date, Prior_Date = (lag(date) over(partition by ticker order by date))
    , Current_open = [open] , Prior_open = (lag([open]) over(partition by ticker order by date))
    , Current_high = [high] , Prior_high = (lag([high]) over(partition by ticker order by date))
    , Current_low = [low] , Prior_low = (lag([low]) over(partition by ticker order by date))
    , Current_close = [close] , Prior_close = (lag([close]) over(partition by ticker order by date))
from dbo.V with (nolock)
)
select * from cte_tenPct
where (Current_open <> 0 and Current_open = Prior_open / 10)
and (Current_high <> 0 and Current_high = Prior_high / 10)
and (Current_low <> 0 and Current_low = Prior_low / 10)
and (Current_close <> 0 and Current_close = Prior_close / 10)

Here are the results (just the relevant parts)

So, I want to basically start the query with: DELETE FROM V where V.Ticker = cte_tenPct.Ticker and V.Date = cte_tenPct.CurrentDate IN --then I would have the select query below. Can someone help me out with the syntax. Thanks

CodePudding user response：

 ;with cte_tenPct as  (
 select exchange, ticker
    , CurrentDate = date, Prior_Date = (lag(date) over(partition by ticker order by date))
    , Current_open = [open] , Prior_open = (lag([open]) over(partition by ticker order by date))
    , Current_high = [high] , Prior_high = (lag([high]) over(partition by ticker order by date))
    , Current_low = [low] , Prior_low = (lag([low]) over(partition by ticker order by date))
    , Current_close = [close] , Prior_close = (lag([close]) over(partition by ticker order by date))
from dbo.V with (nolock)
)
delete from cte_tenPct
where (Current_open <> 0 and Current_open = Prior_open / 10)
and (Current_high <> 0 and Current_high = Prior_high / 10)
and (Current_low <> 0 and Current_low = Prior_low / 10)
and (Current_close <> 0 and Current_close = Prior_close / 10)

Basically you just need to delete instead of selecting the cte resutls.
And of cource you can either delete all entries that exist in the cte results or use a where clause to filter which entries will be deleted.