Updating Data

This page describes how to update data in BigQuery. Note that DML in BigQuery has some limitations and known issues.

This guide does not cover adding rows to existing tables. To load data into BigQuery, see the guides on loading data.

Before you begin

To update data in BigQuery, you must set up billing and ensure that you have write access to the table.

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Manage resources page

  3. Enable billing for your project.

    Enable billing

  4. Ensure that you have write access to your table. If you are the owner of the dataset that contains your table, you probably have write access.

    To set write access to a dataset in Google BigQuery:

    1. Go to the BigQuery web UI.
      Go to the BigQuery web UI
    2. In the navigation, hover on a dataset ID and click the down arrow icon down arrow icon image next to the ID and click Share dataset.
    3. Add a person and give that person edit access, then click Save changes.

Updating data

Follow along with the instructions below by using this sample file, which represents a table with an IP address column that you'd like to mask for anonymization:

The following steps load the sample data into a table and update the values in the ip_address column:

Step 1. Load the JSON file into a UserSessions table.

Step 2. To mask the last octet in every row's ip_address column, run the following DML query:

UPDATE sample_db.UserSessions
SET ip_address = REGEXP_REPLACE(ip_address, r"(\.[0-9]+)$", ".0")

Deleting data

Follow along with the instructions below by using the sample files, which represent a dataset with multiple tables of analytics for user sessions and a table of users to be deleted.

The following steps load the data into three tables, and then delete the users listed in the DeletedUsers table.

Step 1. Load the JSON files into the DeletedUsers, Users, and UserSessions tables, respectively.

Web UI

  1. Open the Google BigQuery web UI.
  2. Hover over your dataset ID.
  3. Click the arrow next to your dataset ID. Arrow next to dataset
  4. Click Create new table.
  5. Select File upload for the Location.
  6. Select JSON (newline delimited) for the File format.
  7. Choose the Table name.
  8. Under Schema enter a Name for each column in the table and select the appropriate Type.

    • Click Add field and repeat until you have entered all the columns in your table.
  9. Click Create table.

The schemas for the sample tables are:

  • DeletedUsers
    • Name id, Type INTEGER
  • Users
    • Name id, Type INTEGER
    • Name date_joined, Type TIMESTAMP
  • UserSessions
    • Name id, Type STRING
    • Name user_id, Type INTEGER
    • Name login_time, Type TIMESTAMP
    • Name logout_time, Type TIMESTAMP
    • Name ip_address, Type STRING


To use the bq command-line tool to create the tables, use the bq load command.

To create the DeleteUsers table

bq load --source_format=NEWLINE_DELIMITED_JSON \
    sample_db.DeletedUsers \
    deletedUsersData.json \

To create the Users table

bq load --source_format=NEWLINE_DELIMITED_JSON \
    sample_db.Users \
    usersData.json \

To create the UserSessions table

bq load --source_format=NEWLINE_DELIMITED_JSON \
    sample_db.UserSessions \
    userSessionsData.json \

Step 2. To delete the information about the users in the DeletedUsers table. Run the following DML queries:

  • Delete from UsersSessions

    DELETE FROM sample_db.UserSessions
    WHERE user_id in (SELECT id from sample_db.DeletedUsers)

  • Delete from Users

    DELETE FROM sample_db.Users
    WHERE id in (SELECT id from sample_db.DeletedUsers)

What's next

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...