28 August 2018 · DataFreak ·       Add to Favorites   Report

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 August 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied