Using select statement to delete rows-CodePudding

I have the below query:

SELECT a.* FROM id_stats a
join id_list b
on a.id = b.id
and a.start_date NOT between b.start_date::date and DATEADD(day,7,b.start_date::date)

that returns the rows I want to see from the id_stats table, but when I try to delete I get an error:

DELETE a FROM id_stats a
join id_list b
on a.id = b.id
and a.start_date NOT between b.start_date::date
and DATEADD(day,7,b.start_date::date)

error:

SQL compilation error: syntax error line 1 at position 7 unexpected 'a'. syntax error line 2 at position 0 unexpected 'join'.

CodePudding user response：

Per the comments above, here is the reference document that explains the syntax. No JOIN and no ON. It's using and where.

https://docs.snowflake.com/en/sql-reference/sql/delete.html#syntax

DELETE FROM id_stats a 
 using id_list b 
 where a.id = b.id 
   and a.start_date NOT between b.start_date::date and DATEADD(day,7,b.start_date::date)