CODEWITHSUNDEEP
sqlgoogle-bigquery
Programmer120
Programmer120

Reputation: 2592

What is the proper syntax for delete query with BigQuery

I have the following query where the ID is not UNIQUE:

delete
      ( SELECT ROW_NUMBER() OVER (PARTITION BY createdOn, id  order by updatedOn) as rn , id FROM `a.tab` ) as t
    WHERE t.rn> 1;

The inner select return the result but the delete fails with:

Error: Syntax error: Unexpected "(" at [2:7]

What is the syntax problem here?

Upvotes: 0

Views: 1722

Answers (2)

Suresh
Suresh

Reputation: 489

Use query as :: delete from t From ( SELECT ROW_NUMBER() OVER (PARTITION BY createdOn, id order by updatedOn) as rn , id FROM a.tab ) as t WHERE t.rn> 1;

Hope this works

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Unlike SQL Server, and a few other databases, Big Query does not allow deleting directly from a CTE. But, we can specify your target table, and then use the row number in the WHERE clause.

DELETE
FROM yourTable AS t1
WHERE (SELECT ROW_NUMBER() OVER (PARTITION BY createdOn, id ORDER BY updatedOn)
       FROM yourTable AS t2
       WHERE t1.id = t2.id) > 1;

The idea here is to correlate the row number value to each row in the delete statement using the id, which is presumably a primary key.

Upvotes: 1

Related Questions