Delete and Truncate statement in BigQuery

Delete statement in BigQuery

Delete statement allows us to delete rows from a table. To perform the delete operation in BigQuery, we should specify the WHERE clause followed by a condition in the DELETE statement.

Syntax

Examples

We have a table called paint_details as below in BigQuery. Let’s delete the rows from this table. Here rc_fin_test_tables is a data set name.

Delete statement example in BigQuery
Delete statement example in BigQuery

Delete specific rows from a table

In this example, we are going to delete the rows which has the paint_id as 484849. Hence we need to specify the WHERE clause with the condition of paint_id = 484849 in DELETE query. The column paint_id is a numeric data type. So we didn’t give the quotes for the the paint_id value.

After executing this statement, the specific row is removed from the table paint_details.

Delete specific row from a BigQuery table
Delete specific row from a BigQuery table

Delete all rows from a table

In other databases, we don’t mention the WHERE clause to delete all rows from a table. But BigQuery will throw an error if we write the DELETE statement without WHERE clause.

Delete all rows without WHER clause in BigQuery
Delete all rows without WHER clause in BigQuery

To avoid this error, we need to mention the DELETE statement with WHERE clause followed by true.

Delete all rows from a table in BigQuery
Delete all rows from a table in BigQuery

After adding the Where clause, the DELETE statement has executed successfully and removed all rows from a table paint_details.

Delete with subqueries

Using subquery, we can identity the rows to be deleted. Then those values are passed to the Where condition of DELETE statement. The subquery can query other tables or perform JOINs with other tables.

We have two tables paint_details & paint_suppliers as below in BigQuery. Let’s say that we want to delete the paint details based on manufacturer name Valspar. To do that, we will write a subquery in Delete statement.

paint_details

paint_details in BigQuery
paint_details in BigQuery

paint_suppliers

paint_suppliers in BigQuery
paint_suppliers in BigQuery

Since both the table has the paint_id column, we can fetch the paint id of manufacturer Valspar using sub query. Then we can pass that value to the DELETE statement which will delete the row from paint_details.

Delete statement with sub query in BigQuery
Delete statement with sub query in BigQuery

Finally the row associated with the manufacturer ‘Valspar‘ is deleted from the table paint_details.

table paint_details after executing DELETE statement
table paint_details after executing DELETE statement

Truncate statement in BigQuery

Truncate statement is used to delete all rows from a table. It is a recommended way to delete all rows from a table instead of DELETE statement.

Since Truncate is a DDL (Data Definition Language) operation, it don’t incur any costs while using this statement. It comes under a category of BigQuery free operations. Also it leaves the table metadata intact, including the table schema, description, and labels.

Example

Let’s delete entire contents of the table paint_details using Truncate statement in BigQuery.

Truncate statement in BigQuery
Truncate statement in BigQuery

As we shown above, Truncate statement has deleted all rows from the table paint_details.

Recommended Articles