无法识别语句中“cte”中“delete”附近的输入

mbjcgjjk 于 2021-06-24 发布在 Hive

关注(0)|答案(2)|浏览(219)

我想把复制品放进去 mytable 如果在 col1 .

WITH CTE AS
(
    SELECT 
        *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RN
    FROM
        mytable
)
DELETE FROM CTE 
WHERE RN <> 1

我有个错误：
无法识别语句中“cte”中“delete”附近的输入

sql Hive

来源：https://stackoverflow.com/questions/56419912/cannot-recognize-input-near-delete-from-cte-in-statement

2条答案

按热度按时间

svmlkihl1#

我认为hive不支持这种语法 DELETE . 试试这个：

DELETE FROM mytable t
    WHERE t.id > (SELECT MIN(t2.id)  -- some sort of unique id
                  FROM t t2
                  WHERE t2.id = t.id
                 );

如果你有完整的副本，那么上面的就行不通了。在hive的最新版本中，您可以使用 MERGE . 在旧版本中：

create table temp_t as
     select distinct t.*
     from t;

truncate table t;

insert into t
    select * from temp_t;

当然，在试这个之前先备份一下table！

赞(0）回复(0）举报 2021-06-24

mftmpeh82#

另一种方法：假设您有唯一的id列。

Delete from MyTable where ID in 
    (SELECT ID FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RN
        FROM mytable) a where RN <> 1)

赞(0）回复(0）举报 2021-06-24

我来回答

无法识别语句中“cte”中“delete”附近的输入

2条答案

相关问题

热门标签

最新问答