Syntax error: Expected "(" or "," or keyword SELECT but got keyword DELETE at [6-CodePudding

--DELETE DUPLICATE VALUE IF HAVE TO
WITH cte AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ride_id ORDER BY started_at) AS row_num
  FROM `case-study-1-bike-share.bike_share.202102`
)
DELETE FROM cte
WHERE row_num > 1;

I want to delete duplicate value but received the following error instead. How do i fix this?

CodePudding user response：

Not all SQL implementations allow modifyable CTEs. But you do not need them (in this case).

You could use EXISTS(...) to check of older observations exist for the same ride_id) . If older ones exist for this, this cannot be the oldest, and can be deleted.

-- DELETE DUPLICATE VALUE IF HAVE TO
DELETE FROM "case-study-1-bike-share.bike_share.202102" d
WHERE EXISTS (
        SELECT *
        FROM "case-study-1-bike-share.bike_share.202102" x      -- same table
        WHERE x.ride_id = d.ride_id                             -- same ride
        AND x.started_at < d.started_at                         -- but older date exists
        )
        ;