CODEWITHSUNDEEP
google-bigquery
Richard
Richard

Reputation: 65510

Deleting rows in BigQuery fails with "Invalid schema update"

I'm trying to delete some rows from a BigQuery table (using standard SQL dialect):

DELETE FROM ocds.releases 
WHERE
  ocid LIKE 'ocds-b5fd17-%'

However, I get the following error:

Query Failed
Error: Invalid schema update. Field packageInfo has changed mode from REQUIRED to NULLABLE
Job ID: ocds-172716:bquijob_2f60927_15d13c97149

It seems as though BigQuery doesn't like deleting rows with a REQUIRED column. Is there any way around this?

Upvotes: 0

Views: 276

Answers (1)

Mingyu Zhong
Mingyu Zhong

Reputation: 531

It has been a known limitation that BigQuery DML doesn't work with tables with required fields (see https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language#known_issues).

We are in the process of removing this limitation. We whitelisted your project today. Please try running your query again in the same project. Let us know if the problem is still there, or if you want to have more projects whitelisted.

Upvotes: 3

Related Questions