Delete and Truncate statement in BigQuery
Contents
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
|
1 2 3 4 5 6 7 8 9 |
#delete specific rows from a table DELETE FROM `<project_id>.<dataset>.<table_name>` WHERE <condition>; #delete all rows from a table DELETE FROM `<project_id>.<dataset>.<table_name>` WHERE true; #delete with subqueries DELETE `<project_id>.<dataset>.<table_name>` t WHERE t.id IN (SELECT id from `<project_id>.<dataset>.<table_name>`) |
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 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.
|
1 2 |
DELETE FROM rc_fin_test_tables.paint_details WHERE paint_id = 484849 ; |
After executing this statement, the specific row is removed from the table paint_details.
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.
To avoid this error, we need to mention the DELETE statement with WHERE clause followed by true.
|
1 |
DELETE FROM rc_fin_test_tables.paint_details true; |
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_suppliers
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.
|
1 2 3 4 |
DELETE rc_fin_test_tables.paint_details t WHERE t.paint_id IN (SELECT paint_id from rc_fin_test_tables.paint_suppliers WHERE manufacturer = 'Valspar'); |
Finally the row associated with the manufacturer ‘Valspar‘ is deleted from the table paint_details.
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.
|
1 |
TRUNCATE TABLE `<project_id>.<dataset>.<table_name>`; |
Example
Let’s delete entire contents of the table paint_details using Truncate statement in BigQuery.
As we shown above, Truncate statement has deleted all rows from the table paint_details.
Recommended Articles
- How to schedule and run BigQuery using Cloud Composer?
- How to run a BigQuery SQL using Python?
- How to execute a Select query on BigQuery using Java?
