28 Aug 18 · DataFreak ·       Add to Favorites  

How to DELETE duplicate records from BigQuery table

For example, your table has duplicate records and you want to keep one record and get rid of the duplicate record, You can use a co-related subquery like the below to cleanup the table

DELETE
FROM duplicates AS d
WHERE (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY loadTime DESC)
       FROM `duplicates` AS d2
       WHERE d.id = d2.id AND d.loadTime = d2.loadTime) > 1;

DataFreak

posted on 28 Aug 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied