Why does the cte return the error that it does not exist?

Question

Here is my code

WITH CTE AS(
    SELECT COUNT(CASE name WHEN 'John' THEN 1 END) OVER (PARTITION BY BlockID ORDER BY Step) AS Johns
    FROM dbo.YourTable)
DELETE FROM CTE
WHERE Johns >= 1;
SELECT *
FROM dbo.YourTable;

It returns me the following error when I run the code in the notebook

ERROR: syntax error at or near "DELETE"

But I can't seem to find any mistake in the query

When I try to do it in online compiler it returns the error that relation "cte" does not exist Maybe this errors can be related?...

Here what I'm trying to do with cte: My first table:

Block_id step name 
1         1    Marie 
1         2    Bob
1         3    John
1         4    Lola
2         1    Alex
2         2    John
2         3    Kate
2         4    Herald
3         1    Alec
3         2    Paul
3         3    Rex

As you can see data frame is sorted by block_id and then by step. I want to delete only in one block_id everything after the row where I have name John(the row with John as well). So the desired output would be

Block_id step name 
1         1    Marie 
1         2    Bob
2         1    Alex
3         1    Alec
3         2    Paul
3         3    Rex

Answer 1

Create a CTE that returns for each Block_id the step of the first John.
Then join the table to the CTE:

WITH cte AS (
  SELECT Block_id, MIN(step) step
  FROM tablename
  WHERE name = 'John'
  GROUP BY Block_id
)
DELETE FROM tablename t
USING cte c
WHERE c.Block_id = t.Block_id AND c.step <= t.step

See the demo.