--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;
我想洗掉重復值但收到以下錯誤。我該如何解決？
uj5u.com熱心網友回復：
并非所有 SQL 實作都允許可修改的 CTE。但是您不需要它們（在這種情況下）。
您可以EXISTS(...)用來檢查舊的觀察是否存在相同的ride_id) 。如果 存在較舊的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
)
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397722.html
上一篇：查詢所有磁區表
下一篇：重寫活動記錄查詢