Data manipulation language (DML) statements in GoogleSQL

The BigQuery data manipulation language (DML) enables you toupdate, insert, and delete data from your BigQuery tables.

For information about how to use DML statements, seeTransform data with data manipulation languageandUpdate partitioned table data using DML.

On-demand query size calculation

If you use on-demand billing, BigQuery charges for datamanipulation language (DML) statements based on the number of bytes processed bythe statement.

For more information about cost estimation, seeEstimate and control costs.

Non-partitioned tables

For non-partitioned tables, the number of bytes processed is calculated asfollows:

  • q = The sum of bytes processed by the DML statement itself, including anycolumns referenced in tables scanned by the DML statement.
  • t = The size of the table being updated by the DML statement before anymodifications are made.
DML statementBytes processed
INSERTq
UPDATEq +t
DELETEq +t
MERGEIf there are onlyINSERT clauses:q.
If there is anUPDATE orDELETE clause:q +t.

To preview how many bytes a statement processes,Check the estimated cost before running a query.

Partitioned tables

For partitioned tables, the number of bytes processed is calculated asfollows:

  • q' = The sum of bytes processed by the DML statement itself, including anycolumns referenced in all partitions scanned by the DML statement.
  • t' = The total size of all partitions being updated by the DML statementbefore any modifications are made.
DML statementBytes processed
INSERTq'
UPDATEq' +t'
DELETEq' +t'
MERGEIf there are onlyINSERT clauses in theMERGE statement:q'.
If there is anUPDATE orDELETE clause in theMERGE statement:q' +t'.

To preview how many bytes a statement processes,Check the estimated cost before running a query.

INSERT statement

Use theINSERT statement when you want to add new rows to a table.

INSERT[INTO]target_name[(column_1[,...,column_n])]inputinput::=VALUES(expr_1[,...,expr_n])[,...,(expr_k_1[,...,expr_k_n])]|SELECT_QUERYexpr::=value_expression|DEFAULT

INSERT statements must comply with the following rules:

  • Column names are optional if the target table is not aningestion-time partitioned table.
  • Duplicate names are not allowed in the list of target columns.
  • Values must be added in the same order as the specified columns.
  • The number of values added must match the number of specified columns.
  • Values must have a type that is compatible with the target column.
  • When the value expression isDEFAULT, thedefault value for the columnis used. If the column has no default value, the value defaults toNULL.

Omitting column names

When the column names are omitted, all columns in the target table are includedin ascending order based on their ordinal positions. If an omitted column hasa default value, then that value is used. Otherwise, the column value isNULL.If the targettable is aningestion-time partitioned table,column names must be specified.

Value type compatibility

Values added with anINSERT statement must be compatible with the targetcolumn's type. A value's type is considered compatible with the target column'stype if one of the following criteria are met:

  • The value type matches the column type exactly. For example, inserting avalue of type INT64 in a column that also has a type of INT64.
  • The value type is one that can be implicitly coerced into another type.

INSERT examples

INSERT using explicit values

INSERTdataset.Inventory(product,quantity)VALUES('top load washer',10),('front load washer',20),('dryer',30),('refrigerator',10),('microwave',20),('dishwasher',30),('oven',5)
+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 |               NULL || dryer             |       30 |               NULL || front load washer |       20 |               NULL || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL || top load washer   |       10 |               NULL |+-------------------+----------+--------------------+

If you set a default value for a column, then you can use theDEFAULT keywordin place of a value to insert the default value:

ALTERTABLEdataset.NewArrivalsALTERCOLUMNquantitySETDEFAULT100;INSERTdataset.NewArrivals(product,quantity,warehouse)VALUES('top load washer',DEFAULT,'warehouse #1'),('dryer',200,'warehouse #2'),('oven',300,'warehouse #3');
+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |      200 | warehouse #2 || oven            |      300 | warehouse #3 || top load washer |      100 | warehouse #1 |+-----------------+----------+--------------+

INSERT SELECT statement

INSERTdataset.Warehouse(warehouse,state)SELECT*FROMUNNEST([('warehouse #1','WA'),('warehouse #2','CA'),('warehouse #3','WA')])
+--------------+-------+|  warehouse   | state |+--------------+-------+| warehouse #1 | WA    || warehouse #2 | CA    || warehouse #3 | WA    |+--------------+-------+

You can also useWITH when usingINSERT SELECT. For example, you canrewrite the previous query usingWITH:

INSERTdataset.Warehouse(warehouse,state)WITHwAS(SELECTARRAY<STRUCT<warehousestring,statestring>>[('warehouse #1','WA'),('warehouse #2','CA'),('warehouse #3','WA')]col)SELECTwarehouse,stateFROMw,UNNEST(w.col)

The following example shows how to copy a table's contents into another table:

INSERTdataset.DetailedInventory(product,quantity,supply_constrained)SELECTproduct,quantity,falseFROMdataset.Inventory
+----------------------+----------+--------------------+----------+----------------+|       product        | quantity | supply_constrained | comments | specifications |+----------------------+----------+--------------------+----------+----------------+| dishwasher           |       30 |              false |       [] |           NULL || dryer                |       30 |              false |       [] |           NULL || front load washer    |       20 |              false |       [] |           NULL || microwave            |       20 |              false |       [] |           NULL || oven                 |        5 |              false |       [] |           NULL || refrigerator         |       10 |              false |       [] |           NULL || top load washer      |       10 |              false |       [] |           NULL |+----------------------+----------+--------------------+----------+----------------+

INSERT VALUES with subquery

The following example shows how to insert a row into a table, where one of thevalues is computed using a subquery:

INSERTdataset.DetailedInventory(product,quantity)VALUES('countertop microwave',(SELECTquantityFROMdataset.DetailedInventoryWHEREproduct='microwave'))
+----------------------+----------+--------------------+----------+----------------+|       product        | quantity | supply_constrained | comments | specifications |+----------------------+----------+--------------------+----------+----------------+| countertop microwave |       20 |               NULL |       [] |           NULL || dishwasher           |       30 |              false |       [] |           NULL || dryer                |       30 |              false |       [] |           NULL || front load washer    |       20 |              false |       [] |           NULL || microwave            |       20 |              false |       [] |           NULL || oven                 |        5 |              false |       [] |           NULL || refrigerator         |       10 |              false |       [] |           NULL || top load washer      |       10 |              false |       [] |           NULL |+----------------------+----------+--------------------+----------+----------------+

INSERT without column names

INSERTdataset.WarehouseVALUES('warehouse #4','WA'),('warehouse #5','NY')

This is theWarehouse table before you run the query:

+--------------+-------+|  warehouse   | state |+--------------+-------+| warehouse #1 | WA    || warehouse #2 | CA    || warehouse #3 | WA    |+--------------+-------+

This is theWarehouse table after you run the query:

+--------------+-------+|  warehouse   | state |+--------------+-------+| warehouse #1 | WA    || warehouse #2 | CA    || warehouse #3 | WA    || warehouse #4 | WA    || warehouse #5 | NY    |+--------------+-------+

INSERT withSTRUCT types

The following example shows how to insert a row into a table, where some ofthe fields areSTRUCT types.

INSERTdataset.DetailedInventoryVALUES('top load washer',10,FALSE,[(CURRENT_DATE,"comment1")],("white","1 year",(30,40,28))),('front load washer',20,FALSE,[(CURRENT_DATE,"comment1")],("beige","1 year",(35,45,30)))

Here is theDetailedInventory table after you run the query:

+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+|      product      | quantity | supply_constrained |                    comments                     |                                           specifications                                           |+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+| front load washer |       20 |              false | [{"created":"2021-02-09","comment":"comment1"}] | {"color":"beige","warranty":"1 year","dimensions":{"depth":"35.0","height":"45.0","width":"30.0"}} || top load washer   |       10 |              false | [{"created":"2021-02-09","comment":"comment1"}] | {"color":"white","warranty":"1 year","dimensions":{"depth":"30.0","height":"40.0","width":"28.0"}} |+-------------------+----------+--------------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------+

INSERT withARRAY types

The following example show how to insert a row into a table, where one of thefields is anARRAY type.

CREATETABLEIFNOTEXISTSdataset.table1(namesARRAY<STRING>);INSERTINTOdataset.table1(names)VALUES(["name1","name2"])

Here is the table after you run the query:

+-------------------+|names|+-------------------+|["name1","name2"]|+-------------------+

INSERT withRANGE types

The following example shows how to insert rows into a table, where thefields areRANGE type.

INSERTmydataset.my_range_table(emp_id,dept_id,duration)VALUES(10,1000,RANGE<DATE>'[2010-01-10, 2010-03-10)'),(10,2000,RANGE<DATE>'[2010-03-10, 2010-07-15)'),(10,2000,RANGE<DATE>'[2010-06-15, 2010-08-18)'),(20,2000,RANGE<DATE>'[2010-03-10, 2010-07-20)'),(20,1000,RANGE<DATE>'[2020-05-10, 2020-09-20)');SELECT*FROMmydataset.my_range_tableORDERBYemp_id;/*--------+---------+--------------------------+ | emp_id | dept_id | duration                 | +--------+---------+--------------------------+ | 10     | 1000    | [2010-01-10, 2010-03-10) | | 10     | 2000    | [2010-03-10, 2010-07-15) | | 10     | 2000    | [2010-06-15, 2010-08-18) | | 20     | 2000    | [2010-03-10, 2010-07-20) | | 20     | 1000    | [2020-05-10, 2020-09-20) | +--------+---------+--------------------------*/

DELETE statement

Use theDELETE statement when you want to delete rows from a table.

DELETE[FROM]target_name[alias]WHEREcondition

To delete all rows in a table, use theTRUNCATE TABLE statement.

To delete all rows in a partition without scanning bytes or consuming slots,seeUsing DML DELETE to delete partitions.

WHERE keyword

Each time you construct aDELETE statement, you must use theWHERE keyword,followed by a condition.

TheWHERE keyword is mandatory for anyDELETE statement.

DELETE examples

DELETE withWHERE clause

DELETEdataset.InventoryWHEREquantity=0

Before:

+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       20 |               NULL || dryer             |       30 |               NULL || front load washer |       10 |               NULL || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL || top load washer   |        0 |               NULL |+-------------------+----------+--------------------+

After:

+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       20 |               NULL || dryer             |       30 |               NULL || front load washer |       10 |               NULL || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL |+-------------------+----------+--------------------+

DELETE with subquery

DELETEdataset.InventoryiWHEREi.productNOTIN(SELECTproductfromdataset.NewArrivals)

Before:

Inventory+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 |               NULL || dryer             |       30 |               NULL || front load washer |       20 |               NULL || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL || top load washer   |       10 |               NULL |+-------------------+----------+--------------------+NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |      200 | warehouse #2 || oven            |      300 | warehouse #3 || top load washer |      100 | warehouse #1 |+-----------------+----------+--------------+

After:

Inventory+-----------------+----------+--------------------+|     product     | quantity | supply_constrained |+-----------------+----------+--------------------+| dryer           |       30 |               NULL || oven            |        5 |               NULL || top load washer |       10 |               NULL |+-----------------+----------+--------------------+

Alternately, you can useDELETE with theEXISTS clause:

DELETEdataset.InventoryWHERENOTEXISTS(SELECT*fromdataset.NewArrivalsWHEREInventory.product=NewArrivals.product)

TRUNCATE TABLE statement

TheTRUNCATE TABLE statement removes all rows from a table but leaves thetable metadata intact, including the table schema, description, and labels.

Note: This statement is a metadata operation and does not incur a charge.
TRUNCATETABLE[[project_name.]dataset_name.]table_name

Where:

  • project_name is the name of the project containing the table. Defaultsto the project that runs this DDL query.

  • dataset_name is the name of the dataset containing the table.

  • table_name is the name of the table to truncate.

Truncating views, materialized views, models, or external tables is notsupported. Quotas and limits for queries apply toTRUNCATE TABLE statements.For more information, seeQuotas and limits.

TRUNCATE TABLE examples

The following example removes all rows from the table namedInventory.

TRUNCATETABLEdataset.Inventory

UPDATE statement

Use theUPDATE statement when you want to update existing rows within a table.

UPDATEtarget_name[[AS]alias]SETset_clause[FROMfrom_clause]WHEREconditionset_clause::=update_item[,...]update_item::=column_name=expression

Where:

  • target_name is the name of a table to update.
  • update_item is the name of column to update and an expression to evaluatefor the updated value. The expression may contain theDEFAULT keyword,which is replaced by the default value for that column.

If the column is aSTRUCT type,column_name can reference a field in theSTRUCT using dot notation. For example,struct1.field1.

WHERE keyword

EachUPDATE statement must include theWHERE keyword, followed by acondition.

To update all rows in the table, useWHERE true.

FROM keyword

AnUPDATE statement can optionally include aFROM clause.

You can use theFROM clause to specify the rows to update in the target table.You can also use columns from joined tables in aSET clause orWHEREcondition.

TheFROM clause join can be a cross join if no condition is specified in theWHERE clause, otherwise it is an inner join. In either case, rows from thetarget table can join with at most one row from theFROM clause.

To specify the join predicate between the table to be updated and tables intheFROM clause, use theWHERE clause. For an example, seeUPDATE using joins.

Caveats:

  • TheSET clause can reference columns from a target table and columns fromanyFROM item in theFROM clause. If there is a name collision,unqualified references are treated as ambiguous.
  • If the target table is present in theFROM clause as a table name, itmust have an alias if you would like to perform a self-join.
  • If a row in the table to be updated joins with zero rows from theFROMclause, then the row isn't updated.
  • If a row in the table to be updated joins with exactly one row from theFROMclause, then the row is updated.
  • If a row in the table to be updated joins with more than one row from theFROM clause, then the query generates the following runtime error:UPDATE/MERGE must match at most one source row for each target row.

UPDATE examples

UPDATE withWHERE clause

The following example updates a table namedInventory by reducing the valueof thequantity field by 10 for all products that contain the stringwasher.Assume that the default value for thesupply_constrained column is set toTRUE.

UPDATEdataset.InventorySETquantity=quantity-10,supply_constrained=DEFAULTWHEREproductlike'%washer%'

Before:

Inventory+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 |               NULL || dryer             |       30 |               NULL || front load washer |       20 |               NULL || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL || top load washer   |       10 |               NULL |+-------------------+----------+--------------------+

After:

Inventory+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       20 |               true || dryer             |       30 |               NULL || front load washer |       10 |               true || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL || top load washer   |        0 |               true |+-------------------+----------+--------------------+

UPDATE using joins

The following example generates a table with inventory totals that includeexisting inventory and inventory from theNewArrivals table, andmarkssupply_constrained asfalse:

UPDATEdataset.InventorySETquantity=quantity+(SELECTquantityFROMdataset.NewArrivalsWHEREInventory.product=NewArrivals.product),supply_constrained=falseWHEREproductIN(SELECTproductFROMdataset.NewArrivals)

Alternately, you can join the tables:

UPDATEdataset.InventoryiSETquantity=i.quantity+n.quantity,supply_constrained=falseFROMdataset.NewArrivalsnWHEREi.product=n.product
Note: The join predicate between Inventory and NewArrivals is specified usingtheWHERE clause.

Before:

Inventory+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 |               NULL || dryer             |       30 |               NULL || front load washer |       20 |               NULL || microwave         |       20 |               NULL || oven              |        5 |               NULL || refrigerator      |       10 |               NULL || top load washer   |       10 |               NULL |+-------------------+----------+--------------------+NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |      200 | warehouse #2 || oven            |      300 | warehouse #3 || top load washer |      100 | warehouse #1 |+-----------------+----------+--------------+

After:

+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 |               NULL || dryer             |      230 |              false || front load washer |       20 |               NULL || microwave         |       20 |               NULL || oven              |      305 |              false || refrigerator      |       10 |               NULL || top load washer   |      110 |              false |+-------------------+----------+--------------------+

UPDATE nested fields

The following example updates nested record fields.

UPDATEdataset.DetailedInventorySETspecifications.color='white',specifications.warranty='1 year'WHEREproductlike'%washer%'

Alternatively, you can update the entire record:

UPDATEdataset.DetailedInventorySETspecifications=STRUCT<colorSTRING,warrantySTRING,dimensionsSTRUCT<depthFLOAT64,heightFLOAT64,widthFLOAT64>>('white','1 year',NULL)WHEREproductlike'%washer%'
+----------------------+----------+--------------------+----------+---------------------------------------------------------+|       product        | quantity | supply_constrained | comments |                     specifications                      |+----------------------+----------+--------------------+----------+---------------------------------------------------------+| countertop microwave |       20 |               NULL |       [] |                                                    NULL || dishwasher           |       30 |              false |       [] | {"color":"white","warranty":"1 year","dimensions":null} || dryer                |       30 |              false |       [] |                                                    NULL || front load washer    |       20 |              false |       [] | {"color":"white","warranty":"1 year","dimensions":null} || microwave            |       20 |              false |       [] |                                                    NULL || oven                 |        5 |              false |       [] |                                                    NULL || refrigerator         |       10 |              false |       [] |                                                    NULL || top load washer      |       10 |              false |       [] | {"color":"white","warranty":"1 year","dimensions":null} |+----------------------+----------+--------------------+----------+---------------------------------------------------------+

UPDATE repeated records

The following example appends an entry to a repeated record in thecommentscolumn for products that contain the stringwasher:

UPDATEdataset.DetailedInventorySETcomments=ARRAY(SELECTcommentFROMUNNEST(comments)AScommentUNIONALLSELECT(CAST('2016-01-01'ASDATE),'comment1'))WHEREproductlike'%washer%'
+----------------------+----------+--------------------+----------------------------------------------------+----------------+|       product        | quantity | supply_constrained |                      comments                      | specifications |+----------------------+----------+--------------------+----------------------------------------------------+----------------+| countertop microwave |       20 |               NULL |                                                 [] |           NULL || dishwasher           |       30 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL || dryer                |       30 |              false |                                                 [] |           NULL || front load washer    |       20 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL || microwave            |       20 |              false |                                                 [] |           NULL || oven                 |        5 |              false |                                                 [] |           NULL || refrigerator         |       10 |              false |                                                 [] |           NULL || top load washer      |       10 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |+----------------------+----------+--------------------+----------------------------------------------------+----------------+

Alternatively, you can use theARRAY_CONCAT function:

UPDATEdataset.DetailedInventorySETcomments=ARRAY_CONCAT(comments,ARRAY<STRUCT<createdDATE,commentSTRING>>[(CAST('2016-01-01'ASDATE),'comment1')])WHEREproductlike'%washer%'

The following example appends a second entry to the repeated record in thecomments column for all rows:

UPDATEdataset.DetailedInventorySETcomments=ARRAY(SELECTcommentFROMUNNEST(comments)AScommentUNIONALLSELECT(CAST('2016-01-01'ASDATE),'comment2'))WHEREtrueSELECTproduct,commentsFROMdataset.DetailedInventory
+----------------------+------------------------------------------------------------------------------------------------------+|       product        |                                               comments                                               |+----------------------+------------------------------------------------------------------------------------------------------+| countertop microwave |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] || dishwasher           | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] || dryer                |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] || front load washer    | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] || microwave            |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] || oven                 |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] || refrigerator         |                                                   [u'{"created":"2016-01-01","comment":"comment2"}'] || top load washer      | [u'{"created":"2016-01-01","comment":"comment1"}', u'{"created":"2016-01-01","comment":"comment2"}'] |+----------------------+------------------------------------------------------------------------------------------------------+

To delete repeated value entries, you can useWHERE ... NOT LIKE:

UPDATEdataset.DetailedInventorySETcomments=ARRAY(SELECTcFROMUNNEST(comments)AScWHEREc.commentNOTLIKE'%comment2%')WHEREtrue
+----------------------+----------+--------------------+----------------------------------------------------+----------------+|       product        | quantity | supply_constrained |                      comments                      | specifications |+----------------------+----------+--------------------+----------------------------------------------------+----------------+| countertop microwave |       20 |               NULL |                                                 [] |           NULL || dishwasher           |       30 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL || dryer                |       30 |              false |                                                 [] |           NULL || front load washer    |       20 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL || microwave            |       20 |              false |                                                 [] |           NULL || oven                 |        5 |              false |                                                 [] |           NULL || refrigerator         |       10 |              false |                                                 [] |           NULL || top load washer      |       10 |              false | [u'{"created":"2016-01-01","comment":"comment1"}'] |           NULL |+----------------------+----------+--------------------+----------------------------------------------------+----------------+

UPDATE statement using join between three tables

The following example setssupply_constrained totrue for all products fromNewArrivals where the warehouse location is in'WA' state.

UPDATEdataset.DetailedInventorySETsupply_constrained=trueFROMdataset.NewArrivals,dataset.WarehouseWHEREDetailedInventory.product=NewArrivals.productANDNewArrivals.warehouse=Warehouse.warehouseANDWarehouse.state='WA'

Note that the join predicate for the join with the updated table(DetailedInventory) must be specified usingWHERE. However, joins betweenthe other tables (NewArrivals andWarehouse) can be specified using anexplicitJOIN ... ON clause. For example, the following query is equivalentto the previous query:

UPDATEdataset.DetailedInventorySETsupply_constrained=trueFROMdataset.NewArrivalsINNERJOINdataset.WarehouseONNewArrivals.warehouse=Warehouse.warehouseWHEREDetailedInventory.product=NewArrivals.productANDWarehouse.state='WA'

Before:

DetailedInventory+----------------------+----------+--------------------+----------+----------------+|       product        | quantity | supply_constrained | comments | specifications |+----------------------+----------+--------------------+----------+----------------+| countertop microwave |       20 |               NULL |       [] |           NULL || dishwasher           |       30 |              false |       [] |           NULL || dryer                |       30 |              false |       [] |           NULL || front load washer    |       20 |              false |       [] |           NULL || microwave            |       20 |              false |       [] |           NULL || oven                 |        5 |              false |       [] |           NULL || refrigerator         |       10 |              false |       [] |           NULL || top load washer      |       10 |              false |       [] |           NULL |+----------------------+----------+--------------------+----------+----------------+New arrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |      200 | warehouse #2 || oven            |      300 | warehouse #3 || top load washer |      100 | warehouse #1 |+-----------------+----------+--------------+Warehouse+--------------+-------+|  warehouse   | state |+--------------+-------+| warehouse #1 | WA    || warehouse #2 | CA    || warehouse #3 | WA    |+--------------+-------+

After:

+----------------------+----------+--------------------+----------+----------------+|       product        | quantity | supply_constrained | comments | specifications |+----------------------+----------+--------------------+----------+----------------+| countertop microwave |       20 |               NULL |       [] |           NULL || dishwasher           |       30 |              false |       [] |           NULL || dryer                |       30 |              false |       [] |           NULL || front load washer    |       20 |              false |       [] |           NULL || microwave            |       20 |              false |       [] |           NULL || oven                 |        5 |               true |       [] |           NULL || refrigerator         |       10 |              false |       [] |           NULL || top load washer      |       10 |               true |       [] |           NULL |+----------------------+----------+--------------------+----------+----------------+

MERGE statement

AMERGE statementis a DML statement that can combineINSERT,UPDATE, andDELETEoperations into a single statement and perform the operations atomically.

Note: If as part of aMERGE a new row is inserted in the target table, thenewly inserted row is not eligible for a match with rows from the source table.Matching is based on the state the tables are in when the query is started.
MERGE[INTO]target_name[[AS]alias]USINGsource_name[[AS]alias]ONmerge_condition{when_clause}+when_clause::=matched_clause|not_matched_by_target_clause|not_matched_by_source_clausematched_clause::=WHENMATCHED[ANDsearch_condition]THEN{merge_update_clause|merge_delete_clause}not_matched_by_target_clause::=WHENNOTMATCHED[BYTARGET][ANDsearch_condition]THENmerge_insert_clausenot_matched_by_source_clause::=WHENNOTMATCHEDBYSOURCE[ANDsearch_condition]THEN{merge_update_clause|merge_delete_clause}merge_condition::=bool_expressionsearch_condition::=bool_expressionmerge_update_clause::=UPDATESETupdate_item[,update_item]*update_item::=column_name=expressionmerge_delete_clause::=DELETEmerge_insert_clause::=INSERT[(column_1[,...,column_n])]inputinput::=VALUES(expr_1[,...,expr_n])|ROWexpr::=expression|DEFAULT

Where:

  • target_name
    target_name is the name of the table you’re changing.
  • source_name
    source_name is a table name or subquery.
  • merge_condition

    AMERGE statement performs aJOIN between the target and the source. Then,depending on the match status (row matched, only in source table, only indestination table), the correspondingWHEN clause is executed. Themerge_condition is used by theJOIN to match rows between source and targettables. Depending on the combination ofWHEN clauses, differentINNER andOUTERJOIN types are applied.

    If the merge_condition isFALSE, the query optimizer avoids using aJOIN.This optimization is referred to as a constant false predicate. A constant falsepredicate is useful when you perform an atomicDELETE on the target plus anINSERT from a source (DELETE withINSERT is also known as aREPLACEoperation).

    If the columns used in themerge_condition both containNULL values, specify something likeX = Y OR (X IS NULL AND Y IS NULL).This lets you avoid the case where the join is based on twoNULL values.NULL = NULL evaluates toNULL instead ofTRUE, and creates duplicaterows in the results.

  • when_clause

    Thewhen_clause has three options:MATCHED,NOT MATCHED BY TARGET andNOT MATCHED BY SOURCE. There must be at least onewhen_clause in eachMERGEstatement.

    Eachwhen_clause can have an optionalsearch_condition. Thewhen_clause is executed for a row if both themerge_condition andsearch_condition are satisfied. When there are multiple qualified clauses,only the firstwhen_clause is executed for a row.

  • matched_clause

    Thematched_clause defines how to update or delete a row in the target tableif that row matches a row in the source table.

    If there is at least onematched_clause performing anUPDATEoperation, a runtime error is returned when multiple rows from the sourcetable match one row from the target table, and you are trying to update ordelete that row in the target table.

  • not_matched_by_target_clause
    Thenot_matched_by_target_clause defines how to insert into the target tableif a row from source table does not match any row in the target table.
  • not_matched_by_source_clause
    Thenot_matched_by_source_clause defines how to update or delete a row inthe target table if that row does not match any row in the source table.

Omitting column names

MERGE examples

Example 1

In the following example, the query adds new items from theInventory table to theDetailedInventory table. For items with low inventory, thesupply_constrained value is set totrue, and comments are added.

MERGEdataset.DetailedInventoryTUSINGdataset.InventorySONT.product=S.productWHENNOTMATCHEDANDquantity <20THENINSERT(product,quantity,supply_constrained,comments)VALUES(product,quantity,true,ARRAY<STRUCT<createdDATE,commentSTRING>>[(DATE('2016-01-01'),'comment1')])WHENNOTMATCHEDTHENINSERT(product,quantity,supply_constrained)VALUES(product,quantity,false)

These are the tables before you run the query:

Inventory+-------------------+----------+|      product      | quantity |+-------------------+----------+| dishwasher        |       30 || dryer             |       30 || front load washer |       20 || microwave         |       20 || oven              |        5 || top load washer   |       10 |+-------------------+----------+
DetailedInventory+----------------------+----------+--------------------+----------+----------------+|       product        | quantity | supply_constrained | comments | specifications |+----------------------+----------+--------------------+----------+----------------+| countertop microwave |       20 |               NULL |       [] |           NULL || front load washer    |       20 |              false |       [] |           NULL || microwave            |       20 |              false |       [] |           NULL || refrigerator         |       10 |              false |       [] |           NULL |+----------------------+----------+--------------------+----------+----------------+

This is theDetailedInventory table after you run the query:

DetailedInventory+----------------------+----------+--------------------+-------------------------------------------------+----------------+|       product        | quantity | supply_constrained |                    comments                     | specifications |+----------------------+----------+--------------------+-------------------------------------------------+----------------+| countertop microwave |       20 |               NULL |                                              [] |           NULL || dishwasher           |       30 |              false |                                              [] |           NULL || dryer                |       30 |              false |                                              [] |           NULL || front load washer    |       20 |              false |                                              [] |           NULL || microwave            |       20 |              false |                                              [] |           NULL || oven                 |        5 |               true | [{"created":"2016-01-01","comment":"comment1"}] |           NULL || refrigerator         |       10 |              false |                                              [] |           NULL || top load washer      |       10 |               true | [{"created":"2016-01-01","comment":"comment1"}] |           NULL |+----------------------+----------+--------------------+-------------------------------------------------+----------------+

Example 2

In the following example, the query merges items from theNewArrivals tableinto theInventory table. If an item is already present inInventory, thequery increments thequantity field. Otherwise, the query inserts a new row.Assume that the default value for thesupply_constrained column is set toNULL.

MERGEdataset.InventoryTUSINGdataset.NewArrivalsSONT.product=S.productWHENMATCHEDTHENUPDATESETquantity=T.quantity+S.quantityWHENNOTMATCHEDTHENINSERT(product,quantity)VALUES(product,quantity)

These are the tables before you run the query:

NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       20 | warehouse #2 || oven            |       30 | warehouse #3 || refrigerator    |       25 | warehouse #2 || top load washer |       10 | warehouse #1 |+-----------------+----------+--------------+
Inventory+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 | false              || dryer             |       30 | false              || front load washer |       20 | false              || microwave         |       20 | false              || oven              |        5 | true               || top load washer   |       10 | true               |+-------------------+----------+--------------------+

This is theInventory table after you run the query:

Inventory+-------------------+----------+--------------------+|      product      | quantity | supply_constrained |+-------------------+----------+--------------------+| dishwasher        |       30 | false              || dryer             |       50 | false              || front load washer |       20 | false              || microwave         |       20 | false              || oven              |       35 | true               || refrigerator      |       25 | NULL               || top load washer   |       20 | true               |+-------------------+----------+--------------------+

Example 3

In the following example, the query increases the quantity of products fromwarehouse #1 by 20 in theNewArrivals table. The query deletes all otherproducts except for those from warehouse #2.

MERGEdataset.NewArrivalsTUSING(SELECT*FROMdataset.NewArrivalsWHEREwarehouse <>'warehouse #2')SONT.product=S.productWHENMATCHEDANDT.warehouse='warehouse #1'THENUPDATESETquantity=T.quantity+20WHENMATCHEDTHENDELETE

This is theNewArrivals table before you run the query:

NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       20 | warehouse #2 || oven            |       30 | warehouse #3 || refrigerator    |       25 | warehouse #2 || top load washer |       10 | warehouse #1 |+-----------------+----------+--------------+

This is theNewArrivals table after you run the query:

NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       20 | warehouse #2 || refrigerator    |       25 | warehouse #2 || top load washer |       30 | warehouse #1 |+-----------------+----------+--------------+

Example 4

In the following example, the query replaces all products like'%washer%' intheInventory table by using the values in theNewArrivals table.

MERGEdataset.InventoryTUSINGdataset.NewArrivalsSONFALSEWHENNOTMATCHEDANDproductLIKE'%washer%'THENINSERT(product,quantity)VALUES(product,quantity)WHENNOTMATCHEDBYSOURCEANDproductLIKE'%washer%'THENDELETE

These are the tables before you run the query:

NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       20 | warehouse #2 || refrigerator    |       25 | warehouse #2 || top load washer |       30 | warehouse #1 |+-----------------+----------+--------------+
Inventory+-------------------+----------+|      product      | quantity |+-------------------+----------+| dishwasher        |       30 || dryer             |       50 || front load washer |       20 || microwave         |       20 || oven              |       35 || refrigerator      |       25 || top load washer   |       20 |+-------------------+----------+

This is theInventory table after you run the query:

Inventory+-----------------+----------+|     product     | quantity |+-----------------+----------+| dryer           |       50 || microwave       |       20 || oven            |       35 || refrigerator    |       25 || top load washer |       30 |+-----------------+----------+

Example 5

In the following example, the query adds a comment in theDetailedInventorytable if the product has a lower than average quantity inInventory table.

MERGEdataset.DetailedInventoryTUSINGdataset.InventorySONT.product=S.productWHENMATCHEDANDS.quantity <(SELECTAVG(quantity)FROMdataset.Inventory)THENUPDATESETcomments=ARRAY_CONCAT(comments,ARRAY<STRUCT<createdDATE,commentSTRING>>[(CAST('2016-02-01'ASDATE),'comment2')])

These are the tables before you run the query:

Inventory+-----------------+----------+|     product     | quantity |+-----------------+----------+| dryer           |       50 || microwave       |       20 || oven            |       35 || refrigerator    |       25 || top load washer |       30 |+-----------------+----------+
DetailedInventory+----------------------+----------+--------------------+-------------------------------------------------+----------------+|       product        | quantity | supply_constrained |                    comments                     | specifications |+----------------------+----------+--------------------+-------------------------------------------------+----------------+| countertop microwave |       20 |               NULL |                                              [] |           NULL || dishwasher           |       30 |              false |                                              [] |           NULL || dryer                |       30 |              false |                                              [] |           NULL || front load washer    |       20 |              false |                                              [] |           NULL || microwave            |       20 |              false |                                              [] |           NULL || oven                 |        5 |               true | [{"created":"2016-01-01","comment":"comment1"}] |           NULL || refrigerator         |       10 |              false |                                              [] |           NULL || top load washer      |       10 |               true | [{"created":"2016-01-01","comment":"comment1"}] |           NULL |+----------------------+----------+--------------------+-------------------------------------------------+----------------+

This is theDetailedInventory table after you run the query:

+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+|       product        | quantity | supply_constrained |                                           comments                                            | specifications |+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+| countertop microwave |       20 |               NULL |                                                                                            [] |           NULL || dishwasher           |       30 |              false |                                                                                            [] |           NULL || dryer                |       30 |              false |                                                                                            [] |           NULL || front load washer    |       20 |              false |                                                                                            [] |           NULL || microwave            |       20 |              false |                                               [{"created":"2016-02-01","comment":"comment2"}] |           NULL || oven                 |        5 |               true |                                               [{"created":"2016-01-01","comment":"comment1"}] |           NULL || refrigerator         |       10 |              false |                                               [{"created":"2016-02-01","comment":"comment2"}] |           NULL || top load washer      |       10 |               true | [{"created":"2016-01-01","comment":"comment1"},{"created":"2016-02-01","comment":"comment2"}] |           NULL |+----------------------+----------+--------------------+-----------------------------------------------------------------------------------------------+----------------+

Example 6

In the following example, the query increases the inventory of products from thewarehouse inCA. The products from other states are deleted, and any productthat is not present in theNewArrivals table is unchanged.

MERGEdataset.InventoryTUSING(SELECTproduct,quantity,stateFROMdataset.NewArrivalst1JOINdataset.Warehouset2ONt1.warehouse=t2.warehouse)SONT.product=S.productWHENMATCHEDANDstate='CA'THENUPDATESETquantity=T.quantity+S.quantityWHENMATCHEDTHENDELETE

These are the tables before you run the query:

Warehouse+--------------+-------+|  warehouse   | state |+--------------+-------+| warehouse #1 | WA    || warehouse #2 | CA    || warehouse #3 | WA    |+--------------+-------+
NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       20 | warehouse #2 || refrigerator    |       25 | warehouse #2 || top load washer |       30 | warehouse #1 |+-----------------+----------+--------------+
Inventory+-----------------+----------+|     product     | quantity |+-----------------+----------+| dryer           |       50 || microwave       |       20 || oven            |       35 || refrigerator    |       25 || top load washer |       30 |+-----------------+----------+

This is theInventory table after you run the query:

Inventory+--------------+----------+|   product    | quantity |+--------------+----------+| dryer        |       70 || microwave    |       20 || oven         |       35 || refrigerator |       50 |+--------------+----------+

Example 7

In the following example, a runtime error is returned because the query attemptsto update a target table when the source contains more than one matched row. Toresolve the error, you need to change themerge_condition orsearch_conditionto avoid duplicate matches in the source.

MERGEdataset.InventoryTUSINGdataset.NewArrivalsSONT.product=S.productWHENMATCHEDTHENUPDATESETquantity=T.quantity+S.quantity

These are the tables before you run the query:

NewArrivals+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       10 | warehouse #2 || dryer           |       20 | warehouse #1 || refrigerator    |       25 | warehouse #2 || top load washer |       30 | warehouse #1 |+-----------------+----------+--------------+
Inventory+--------------+----------+|   product    | quantity |+--------------+----------+| dryer        |       70 || microwave    |       20 || oven         |       35 || refrigerator |       50 |+--------------+----------+

When you run the query, the following error is returned:

UPDATE/MERGE must match at most one source row for each target row

Example 8

In the following example, all of the products in theNewArrivals table arereplaced with values from the subquery. TheINSERT clause does not specifycolumn names for either the target table or the source subquery.

MERGEdataset.NewArrivalsUSING(SELECT*FROMUNNEST([('microwave',10,'warehouse #1'),('dryer',30,'warehouse #1'),('oven',20,'warehouse #2')]))ONFALSEWHENNOTMATCHEDTHENINSERTROWWHENNOTMATCHEDBYSOURCETHENDELETE

This is theNewArrivals table before you run the query:

+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| dryer           |       10 | warehouse #2 || dryer           |       20 | warehouse #1 || refrigerator    |       25 | warehouse #2 || top load washer |       30 | warehouse #1 |+-----------------+----------+--------------+

This is theNewArrivals table after you run the query:

+-----------------+----------+--------------+|     product     | quantity |  warehouse   |+-----------------+----------+--------------+| microwave       |       10 | warehouse #1 || dryer           |       30 | warehouse #1 || oven            |       20 | warehouse #2 |+-----------------+----------+--------------+

Tables used in examples

The example queries in this document use the following tables.

Inventory table

[{"name":"product","type":"string"},{"name":"quantity","type":"integer"},{"name":"supply_constrained","type":"boolean"}]

DDL statement to create this table:

CREATEORREPLACETABLEdataset.Inventory(productSTRING,quantityINT64,supply_constrainedBOOLEAN);

NewArrivals table

[{"name":"product","type":"string"},{"name":"quantity","type":"integer"},{"name":"warehouse","type":"string"}]

DDL statement to create this table:

CREATEORREPLACETABLEdataset.NewArrivals(productSTRING,quantityINT64,warehouseSTRING);

Warehouse table

[{"name":"warehouse","type":"string"},{"name":"state","type":"string"}]

DDL statement to create this table:

CREATEORREPLACETABLEdataset.Warehouse(warehouseSTRING,stateSTRING);

DetailedInventory table

[{"name":"product","type":"string"},{"name":"quantity","type":"integer"},{"name":"supply_constrained","type":"boolean"},{"name":"comments","type":"record","mode":"repeated","fields":[{"name":"created","type":"date"},{"name":"comment","type":"string"}]},{"name":"specifications","type":"record","fields":[{"name":"color","type":"string"},{"name":"warranty","type":"string"},{"name":"dimensions","type":"record","fields":[{"name":"depth","type":"float"},{"name":"height","type":"float"},{"name":"width","type":"float"}]}]}]

DDL statement to create this table:

CREATEORREPLACETABLEdataset.DetailedInventory(productSTRING,quantityINT64,supply_constrainedBOOLEAN,commentsARRAY<STRUCT<createdDATE,commentSTRING>>,specificationsSTRUCT<colorSTRING,warrantySTRING,dimensionsSTRUCT<depthFLOAT64,heightFLOAT64,widthFLOAT64>>);

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.