语法错误:预期"(“或"，”或关键字选择，但得到关键字删除在[6:1]

Question

--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;

我想删除重复的值，但是收到了以下错误。我该怎么解决这个问题？

Answer 1

并非所有SQL实现都允许修改CTE。但您不需要它们(在这种情况下)。

您可以使用EXISTS(...)来检查同一ride_id存在的旧观测)。如果this存在较旧的this，则this不能是最老的，可以删除。

-- 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
        )
        ;