CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
tester test
tester test

Reputation: 53

I want to delete all the records return by below query

I want to use delete instead of select clause(snowflake).

WITH duplicates as(
 select row_number()over(partition by ASCII,keyt,prodt,counter,batch_id ,batch_time,overall_level,is_f,is_s,is_c order
                         by batch_time DESC)as r,
                 * from temp_table
                where batch_time BETWEEN '2021-12-01' and '2021-12-31')
                select count(*) from duplicates as d where r>1 order by keyt;

Upvotes: 0

Views: 290

Answers (1)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1608

from the query you that you have given it looks like you don't have column which makes up the unique key, so one option is as follows.

--- not tested

CREATE TABLE new_table LIKE temp_table ;
INSERT INTO new_table SELECT DISTINCT * FROM temp_table;
ALTER TABLE temp_table SWAP WITH new_table;

Upvotes: 1

Related Questions