GoogleSQL data manipulation language

The GoogleSQL data manipulation language (DML) lets you update, insert, and delete data in GoogleSQL tables.

For information about how to use DML statements, see Inserting, updating, and deleting data using Data Manipulation Language . You can also modify data using mutations .

Tables used in examples

  CREATE 
  
 TABLE 
  
 Singers 
  
 ( 
  
 SingerId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 FirstName 
  
 STRING 
 ( 
 1024 
 ), 
  
 LastName 
  
 STRING 
 ( 
 1024 
 ), 
  
 BirthDate 
  
 DATE 
 , 
  
 Status 
  
 STRING 
 ( 
 1024 
 ), 
  
 LastUpdated 
  
 TIMESTAMP 
 , 
  
 SingerInfo 
  
 googlesql 
 . 
 example 
 . 
 SingerInfo 
 , 
  
 AlbumInfo 
  
 googlesql 
 . 
 example 
 . 
 Album 
 , 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 SingerId 
 ); 
 CREATE 
  
 TABLE 
  
 AlbumInfo 
  
 ( 
  
 SingerId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 AlbumId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 AlbumTitle 
  
 STRING 
 ( 
 MAX 
 ), 
  
 MarketingBudget 
  
 INT64 
 , 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 SingerId 
 , 
  
 AlbumId 
 ), 
  
 INTERLEAVE 
  
 IN 
  
 PARENT 
  
 Singers 
  
 ON 
  
 DELETE 
  
 CASCADE 
 ; 
 CREATE 
  
 TABLE 
  
 Songs 
  
 ( 
  
 SingerId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 AlbumId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 TrackId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 SongName 
  
 STRING 
 ( 
 MAX 
 ), 
  
 Duration 
  
 INT64 
 , 
  
 SongGenre 
  
 STRING 
 ( 
 25 
 ), 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 SingerId 
 , 
  
 AlbumId 
 , 
  
 TrackId 
 ), 
  
 INTERLEAVE 
  
 IN 
  
 PARENT 
  
 AlbumInfo 
  
 ON 
  
 DELETE 
  
 CASCADE 
 ; 
 CREATE 
  
 TABLE 
  
 Concerts 
  
 ( 
  
 VenueId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 SingerId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 ConcertDate 
  
 DATE 
  
 NOT 
  
 NULL 
 , 
  
 BeginTime 
  
 TIMESTAMP 
 , 
  
 EndTime 
  
 TIMESTAMP 
 , 
  
 TicketPrices 
  
 ARRAY<INT64> 
 , 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 VenueId 
 , 
  
 SingerId 
 , 
  
 ConcertDate 
 ); 
 CREATE 
  
 TABLE 
  
 AckworthSingers 
  
 ( 
  
 SingerId 
  
 INT64 
  
 NOT 
  
 NULL 
 , 
  
 FirstName 
  
 STRING 
 ( 
 1024 
 ), 
  
 LastName 
  
 STRING 
 ( 
 1024 
 ), 
  
 BirthDate 
  
 DATE 
 , 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 SingerId 
 ); 
 CREATE 
  
 TABLE 
  
 Fans 
  
 ( 
  
 FanId 
  
 STRING 
 ( 
 36 
 ) 
  
 DEFAULT 
  
 ( 
 GENERATE_UUID 
 ()), 
  
 FirstName 
  
 STRING 
 ( 
 1024 
 ), 
  
 LastName 
  
 STRING 
 ( 
 1024 
 ), 
 ) 
  
 PRIMARY 
  
 KEY 
 ( 
 FanId 
 ); 
 

Definitions for protocol buffers used in examples

  package 
  
 googlesql 
 . 
 example 
 ; 
 message 
  
 SingerInfo 
  
 { 
  
 optional 
  
 string 
  
 nationality 
  
 = 
  
 1 
 ; 
  
 repeated 
  
 Residence 
  
 residence 
  
 = 
  
 2 
 ; 
  
 message 
  
 Residence 
  
 { 
  
 required 
  
 int64 
  
 start_year 
  
 = 
  
 1 
 ; 
  
 optional 
  
 int64 
  
 end_year 
  
 = 
  
 2 
 ; 
  
 optional 
  
 string 
  
 city 
  
 = 
  
 3 
 ; 
  
 optional 
  
 string 
  
 country 
  
 = 
  
 4 
 ; 
  
 } 
 } 
 message 
  
 Album 
  
 { 
  
 optional 
  
 string 
  
 title 
  
 = 
  
 1 
 ; 
  
 optional 
  
 int64 
  
 tracks 
  
 = 
  
 2 
 ; 
  
 repeated 
  
 string 
  
 comments 
  
 = 
  
 3 
 ; 
  
 repeated 
  
 Song 
  
 song 
  
 = 
  
 4 
 ; 
  
 message 
  
 Song 
  
 { 
  
 optional 
  
 string 
  
 songtitle 
  
 = 
  
 1 
 ; 
  
 optional 
  
 int64 
  
 length 
  
 = 
  
 2 
 ; 
  
 repeated 
  
 Chart 
  
 chart 
  
 = 
  
 3 
 ; 
  
 message 
  
 Chart 
  
 { 
  
 optional 
  
 string 
  
 chartname 
  
 = 
  
 1 
 ; 
  
 optional 
  
 int64 
  
 rank 
  
 = 
  
 2 
 ; 
  
 } 
  
 } 
 } 
 

Notation used in the syntax

• Square brackets [ ] indicate optional clauses.
• Parentheses ( ) indicate literal parentheses.
• The vertical bar | indicates a logical OR.
• Curly braces { } enclose a set of options.
• A comma followed by an ellipsis indicates that the preceding item can repeat in a comma-separated list. item [, ...] indicates one or more items, and [item, ...] indicates zero or more items.
• A comma , indicates the literal comma.
• Angle brackets <> indicate literal angle brackets.
• A colon : indicates a definition.
• Uppercase words, such as INSERT , are keywords.

INSERT statement

Use the INSERT statement to add new rows to a table. The INSERT statement can insert one or more rows specified by value expressions, or zero or more rows produced by a query. The statement by default returns the number of rows inserted into the table.

  INSERT 
  
 [[ 
 OR 
 ] 
  
 IGNORE 
  
 | 
  
 UPDATE 
 ] 
 [ 
 INTO 
 ] 
  
 table_name 
  
 ( 
 column_name_1 
  
 [, 
  
 ..., 
  
 column_name_n 
 ] 
  
 ) 
  
 input 
  
 [ 
 return_clause 
 ] 
 input 
 : 
  
 VALUES 
  
 ( 
 row_1_column_1_expr 
  
 [, 
  
 ..., 
  
 row_1_column_n_expr 
  
 ] 
  
 ) 
  
 [, 
  
 ..., 
  
 ( 
 row_k_column_1_expr 
  
 [, 
  
 ..., 
  
 row_k_column_n_expr 
  
 ] 
  
 ) 
  
 ] 
 | 
  
 select_query 
 expr 
 : 
  
 value_expression 
  
 | 
  
 DEFAULT 
 return_clause 
 : 
  
 THEN 
  
 RETURN 
  
 [ 
  
 WITH 
  
 ACTION 
  
 [ 
  
 AS 
  
 alias 
  
 ] 
  
 ] 
  
 { 
  
 select_all 
  
 | 
  
 expression 
  
 [ 
  
 [ 
  
 AS 
  
 ] 
  
 alias 
  
 ] 
  
 } 
  
 [, 
  
 ...] 
 select_all 
 : 
  
 [ 
  
 table_name 
 . 
  
 ] 
 * 
  
 [ 
  
 EXCEPT 
  
 ( 
  
 column_name 
  
 [, 
  
 ...] 
  
 ) 
  
 ] 
  
 [ 
  
 REPLACE 
  
 ( 
  
 expression 
  
 [ 
  
 AS 
  
 ] 
  
 column_name 
  
 [, 
  
 ...] 
  
 ) 
  
 ] 
 

INSERT statements must comply with these rules:

• The column names can be in any order.
• Duplicate names are not allowed in the list of columns.
• The number of columns must match the number of values.
• GoogleSQL matches the values in the VALUES clause or the select query positionally with the column list.
• Each value must be type compatible with its associated column.
• The values must comply with any constraints in the schema, for example, unique secondary indexes.
• All non-null columns must appear in the column list, and have a non-null value specified.

If a statement does not comply with the rules, Spanner raises an error and the entire statement fails.

If the statement attempts to insert a duplicate row, as determined by the primary key, then the entire statement fails.

Value type compatibility

Values that you add in an INSERT statement must be compatible with the target column's type. A value's type is compatible with the target column's type if the value meets one of the following criteria:

• The value type matches the column type exactly. For example, inserting a value of type INT64 in a column that has a type of INT64 is compatible.
• GoogleSQL can implicitly coerce the value into the target type.

Default values

Use the DEFAULT keyword to insert the default value of a column. If a column is not included in the list, GoogleSQL assigns the default value of the column. If the column has no defined default value, NULL is assigned to the column.

The use of default values is subject to current Spanner limits, including the mutation limit. If a column has a default value and it is used in an insert or update, the column is counted as one mutation. For example, assuming that table T has three columns and that col_a has a default value, the following inserts each result in three mutations:

  INSERT 
  
 INTO 
  
 T 
  
 ( 
 id 
 , 
  
 col_a 
 , 
  
 col_b 
 ) 
  
 VALUES 
  
 ( 
 1 
 , 
  
 DEFAULT 
 , 
  
 1 
 ); 
 INSERT 
  
 INTO 
  
 T 
  
 ( 
 id 
 , 
  
 col_a 
 , 
  
 col_b 
 ) 
  
 VALUES 
  
 ( 
 2 
 , 
  
 200 
 , 
  
 2 
 ); 
 INSERT 
  
 INTO 
  
 T 
  
 ( 
 id 
 , 
  
 col_b 
 ) 
  
 VALUES 
  
 ( 
 3 
 , 
  
 3 
 ); 
 

For more information about default column values, see the DEFAULT ( expression ) clause in CREATE TABLE .

For more information about mutations, see What are mutations? .

INSERT OR IGNORE

Use the INSERT OR IGNORE clause to insert new rows that don't exist in the table. If the primary key of the row already exists, then the row is ignored. For an INSERT OR IGNORE query that inserts multiple rows or inserts from a subquery, only the new rows are inserted. Rows where the primary key already exists are ignored.

For example, if the primary key is SingerId and the table already contains a SingerId of 7, then in the following example, INSERT would insert the first row and ignore the second row:

  INSERT 
  
 OR 
  
 IGNORE 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 , 
  
 Birthdate 
 , 
  
 Status 
 , 
  
 SingerInfo 
 ) 
 VALUES 
  
 ( 
 5 
 , 
  
 "Zak" 
 , 
  
 "Sterling" 
 , 
  
 "1996-03-12" 
 , 
  
 "active" 
 , 
  
 "nationality:'USA'" 
 ), 
  
 ( 
 7 
 , 
  
 "Edie" 
 , 
  
 "Silver" 
 , 
  
 "1998-01-23" 
 , 
  
 "active" 
 , 
  
 "nationality:'USA'" 
 ); 
 

You can use INSERT OR IGNORE in single or batch DML requests using the executeBatchDml API.

INSERT OR UPDATE

Use the INSERT OR UPDATE clause to insert or update a row. If the primary key is not found, a new row is inserted. If a row with the primary key already exists in the table, then it is updated with the values that you specify in the statement.

For example, in the following statement, INSERT OR UPDATE modifies the column value of Status from active to inactive in the existing table with the primary key SingerId of 5 .

  INSERT 
  
 OR 
  
 UPDATE 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 Status 
 ) 
 VALUES 
  
 ( 
 5 
 , 
  
 "inactive" 
 ); 
 

If the row does not exist, the previous statement inserts a new row with values in the specified fields.

You can use INSERT OR UPDATE in single or batch DML requests using the executeBatchDml API.

THEN RETURN

Use the THEN RETURN clause to return the results of the INSERT operation and selected data from the newly inserted rows. This clause is especially useful for retrieving values of columns with default values, generated columns, and auto-generated keys, without having to use additional SELECT statements.

Use the THEN RETURN clause to capture expressions based on newly inserted rows that include the following:

• WITH ACTION : An optional clause that adds a string column called ACTION to the result row set. Each value in this column represents the type of action that was applied to the column during statement execution. Values include INSERT , DELETE , and UPDATE . The ACTION column is appended as the last output column.
• * : Returns all columns.
• table_name.* : Returns all columns from the table. You cannot use the .* expression with other expressions, including field access.
• EXCEPT ( column_name [, ...] ) : Specifies the columns to exclude from the result. All matching column names are omitted from the output.
• REPLACE ( expression [ AS ] column_name [, ...] ) : Specifies one or more expression AS identifier clauses. Each identifier must match a column name from the table_name.* statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause. Note that the value that gets inserted into the table is not replaced, just the value returned by the THEN RETURN clause.
• expression : Represents a column name of the table specified by table_name or an expression that uses any combination of such column names. Column names are valid if they belong to columns of the table_name . Excluded expressions include aggregate and analytic functions.
• alias : Represents a temporary name for an expression in the query.

For instructions and code samples, see Modify data with the returning DML statements .

INSERT examples

INSERT using literal values examples

The following example adds two rows to the Singers table.

  INSERT 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 , 
  
 SingerInfo 
 ) 
 VALUES 
 ( 
 1 
 , 
  
 'Marc' 
 , 
  
 'Richards' 
 , 
  
 "nationality: 'USA'" 
 ), 
  
 ( 
 2 
 , 
  
 'Catalina' 
 , 
  
 'Smith' 
 , 
  
 "nationality: 'Brazil'" 
 ), 
  
 ( 
 3 
 , 
  
 "Andrew" 
 , 
  
 "Duneskipper" 
 , 
  
 NULL 
 ); 
 

These are the two new rows in the table:

INSERT using a SELECT statement example

The following example shows how to copy the data from one table into another table using a SELECT statement as the input:

  INSERT 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 ) 
 SELECT 
  
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 FROM 
  
 AckworthSingers 
 ; 
 

If the Singers table had no rows, and the AckworthSingers table had three rows, then there are now three rows in the Singers table:

The following example shows how to use UNNEST to return a table that is the input to the INSERT command.

  INSERT 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 ) 
 SELECT 
  
 * 
 FROM 
  
 UNNEST 
  
 ([( 
 4 
 , 
  
 'Lea' 
 , 
  
 'Martin' 
 ), 
  
 ( 
 5 
 , 
  
 'David' 
 , 
  
 'Lomond' 
 ), 
  
 ( 
 6 
 , 
  
 'Elena' 
 , 
  
 'Campbell' 
 )]); 
 

After adding these three additional rows to the table from the previous example, there are six rows in the Singers table:

INSERT using a subquery example

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

  INSERT 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 ) 
 VALUES 
  
 ( 
 4 
 , 
  
 ( 
 SELECT 
  
 FirstName 
  
 FROM 
  
 AckworthSingers 
  
 WHERE 
  
 SingerId 
  
 = 
  
 4 
 )); 
 

The following tables show the data before the statement is executed.

Singers

AckworthSingers

The following table shows the data after the statement is executed.

Singers

To include multiple columns, you include multiple subqueries:

  INSERT 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 ) 
 VALUES 
  
 ( 
 4 
 , 
  
 ( 
 SELECT 
  
 FirstName 
  
 FROM 
  
 AckworthSingers 
  
 WHERE 
  
 SingerId 
  
 = 
  
 4 
 ), 
  
 ( 
 SELECT 
  
 LastName 
  
 FROM 
  
 AckworthSingers 
  
 WHERE 
  
 SingerId 
  
 = 
  
 4 
 )); 
 

INSERT with THEN RETURN examples

The following query inserts two rows into a table, uses THEN RETURN to fetch the SingerId column from these rows, and computes a new column called FullName .

  INSERT 
  
 INTO 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 ) 
 VALUES 
  
 ( 
 7 
 , 
  
 'Melissa' 
 , 
  
 'Garcia' 
 ), 
  
 ( 
 8 
 , 
  
 'Russell' 
 , 
  
 'Morales' 
 ) 
 THEN 
  
 RETURN 
  
 SingerId 
 , 
  
 FirstName 
  
 || 
  
 ' ' 
  
 || 
  
 LastName 
  
 AS 
  
 FullName 
 ; 
 

The following table shows the query result:

The following query inserts a row to the Fans table. Spanner automatically generates a Version 4 UUID for the primary key FanId , and returns it using the THEN RETURN clause.

  INSERT 
  
 INTO 
  
 Fans 
  
 ( 
 FirstName 
 , 
  
 LastName 
 ) 
 VALUES 
  
 ( 
 'Melissa' 
 , 
  
 'Garcia' 
 ) 
 THEN 
  
 RETURN 
  
 FanId 
 ; 
 

The following table shows the query result:

The following query tries to insert or update a row into a table. It uses THEN RETURN to fetch the modified row and WITH ACTION to show the modified row action type.

  INSERT 
  
 OR 
  
 UPDATE 
  
 Singers 
  
 ( 
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 ) 
 VALUES 
  
 ( 
 7 
 , 
  
 'Melissa' 
 , 
  
 'Gartner' 
 ) 
 THEN 
  
 RETURN 
  
 WITH 
  
 ACTION 
  
 SingerId 
 , 
  
 FirstName 
  
 || 
  
 ' ' 
  
 || 
  
 LastName 
  
 AS 
  
 FullName 
 ; 
 

INSERT OR IGNORE example

The following query inserts a row in the Singers table for singers with an ID between 10 and 100. If an ID already exists in Singers , it's ignored.

  INSERT 
  
 OR 
  
 IGNORE 
  
 INTO 
  
 Singers 
  
 ( 
  
 SingerId 
 , 
  
 FirstName 
 , 
  
 LastName 
 , 
  
 BirthDate 
 , 
  
 Status 
 , 
  
 SingerInfo 
 ) 
  
 ( 
 SELECT 
  
 id 
 , 
  
 fname 
 , 
  
 lname 
 , 
  
 dob 
 , 
  
 status 
 , 
  
 info 
  
 FROM 
  
 latest_album 
  
 WHERE 
  
 id 
 > 
 10 
  
 AND 
  
 id 
 < 
 100 
 ); 
 

DELETE statement

Use the DELETE statement to delete rows from a table.

  [ 
 statement_hint_expr 
 ] 
  
 DELETE 
  
 [ 
 FROM 
 ] 
  
 table_name 
  
 [ 
 table_hint_expr 
 ] 
  
 [[ 
 AS 
 ] 
  
 alias 
 ] 
  
 WHERE 
  
 condition 
  
 [ 
 return_clause 
 ]; 
 statement_hint_expr 
 : 
  
 '@{' 
  
 statement_hint_key 
  
 = 
  
 statement_hint_value 
  
 '}' 
 table_hint_expr 
 : 
  
 '@{' 
  
 table_hint_key 
  
 = 
  
 table_hint_value 
  
 '}' 
 return_clause 
 : 
  
 THEN 
  
 RETURN 
  
 [ 
  
 WITH 
  
 ACTION 
  
 [ 
  
 AS 
  
 alias 
  
 ] 
  
 ] 
  
 { 
  
 select_all 
  
 | 
  
 expression 
  
 [ 
  
 [ 
  
 AS 
  
 ] 
  
 alias 
  
 ] 
  
 } 
  
 [, 
  
 ...] 
 select_all 
 : 
  
 [ 
  
 table_name 
 . 
  
 ] 
 * 
  
 [ 
  
 EXCEPT 
  
 ( 
  
 column_name 
  
 [, 
  
 ...] 
  
 ) 
  
 ] 
  
 [ 
  
 REPLACE 
  
 ( 
  
 expression 
  
 [ 
  
 AS 
  
 ] 
  
 column_name 
  
 [, 
  
 ...] 
  
 ) 
  
 ] 
 

WHERE clause

The WHERE clause is required. This requirement can help prevent accidentally deleting all the rows in a table. To delete all rows in a table, set the condition to true :

  DELETE 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 true 
 ; 
 

The WHERE clause can contain any valid SQL statement, including a subquery that refers to other tables.

Aliases

The WHERE clause has an implicit alias to table_name . This alias lets you reference columns in table_name without qualifying them with table_name . For example, if your statement started with DELETE FROM Singers , then you could access any columns of Singers in the WHERE clause. In this example, FirstName is a column in the Singers table:

  DELETE 
  
 FROM 
  
 Singers 
  
 WHERE 
  
 FirstName 
  
 = 
  
 'Alice' 
 ; 
 

You can also create an explicit alias using the optional AS keyword. For more details on aliases, see Query syntax .

THEN RETURN

With the optional THEN RETURN clause, you can obtain data from rows that are being deleted in a table. To learn more about the values that you can use in this clause, see THEN RETURN .

Statement hints

statement_hint_expr is a statement-level hint. The following hints are supported:

Table hints

table_hint_expr is a hint for accessing the table. The following hints are supported:

DELETE examples

DELETE with WHERE clause example

The following DELETE statement deletes all singers whose first name is Alice .

  DELETE 
  
 FROM 
  
 Singers 
  
 WHERE 
  
 FirstName 
  
 = 
  
 'Alice' 
 ; 
 

The following table shows the data before the statement is executed.

The following table shows the data after the statement is executed.

DELETE with subquery example

The following statement deletes any singer in SINGERS whose first name is not in AckworthSingers .

  DELETE 
  
 FROM 
  
 Singers 
 WHERE 
  
 FirstName 
  
 NOT 
  
 IN 
  
 ( 
 SELECT 
  
 FirstName 
  
 from 
  
 AckworthSingers 
 ); 
 

The following table shows the data before the statement is executed.

Singers

AckworthSingers

The following table shows the data after the statement is executed.

Singers

DELETE with THEN RETURN example

The following query deletes all rows in a table that contains a singer called Melissa and returns all columns in the deleted rows except the LastUpdated column.

  DELETE 
  
 FROM 
  
 Singers 
  
 WHERE 
  
 Firstname 
  
 = 
  
 'Melissa' 
 THEN 
  
 RETURN 
  
 * 
  
 EXCEPT 
  
 ( 
 LastUpdated 
 ); 
 

The following table shows the query result:

UPDATE statement

Use the UPDATE statement to update existing rows in a table.

  [ 
 statement_hint_expr 
 ] 
  
 UPDATE 
  
 table_name 
  
 [ 
 table_hint_expr 
 ] 
  
 [[ 
 AS 
 ] 
  
 alias 
 ] 
 SET 
  
 update_item 
  
 [, 
  
 ...] 
 WHERE 
  
 condition 
  
 [ 
 return_clause 
 ]; 
 update_item 
 : 
  
 column_name 
  
 = 
  
 { 
  
 expression 
  
 | 
  
 DEFAULT 
  
 } 
 statement_hint_expr 
 : 
  
 '@{' 
  
 statement_hint_key 
  
 = 
  
 statement_hint_value 
  
 '}' 
 table_hint_expr 
 : 
  
 '@{' 
  
 table_hint_key 
  
 = 
  
 table_hint_value 
  
 '}' 
 return_clause 
 : 
  
 THEN 
  
 RETURN 
  
 [ 
  
 WITH 
  
 ACTION 
  
 [ 
  
 AS 
  
 alias 
  
 ] 
  
 ] 
  
 { 
  
 select_all 
  
 | 
  
 expression 
  
 [ 
  
 [ 
  
 AS 
  
 ] 
  
 alias 
  
 ] 
  
 } 
  
 [, 
  
 ...] 
 select_all 
 : 
  
 [ 
  
 table_name 
 . 
  
 ] 
 * 
  
 [ 
  
 EXCEPT 
  
 ( 
  
 column_name 
  
 [, 
  
 ...] 
  
 ) 
  
 ] 
  
 [ 
  
 REPLACE 
  
 ( 
  
 expression 
  
 [ 
  
 AS 
  
 ] 
  
 column_name 
  
 [, 
  
 ...] 
  
 ) 
  
 ] 
 

Where:

• table_name is the name of a table to update.
• The SET clause is a list of update_items to perform on each row where the WHERE condition is true.
• expression is an update expression. The expression can be a literal, a SQL expression, or a SQL subquery.

• statement_hint_expr is a statement-level hint. The following hints are supported:

  statement_hint_key	statement_hint_value	Description
  PDML_MAX_PARALLELISM	An integer between 1 to 1000	Sets the maximum parallelism for Partitioned DML queries. This hint is only valid with Partitioned DML query execution mode.

• table_hint_expr is a hint for accessing the table. The following hints are supported:

  table_hint_key	table_hint_value	Description
  FORCE_INDEX	Index name	Use specified index when querying rows to be updated.
  FORCE_INDEX	_BASE_TABLE	Don't use an index. Instead, scan the base table.

UPDATE statements must comply with the following rules:

• A column can appear only once in the SET clause.
• The columns in the SET clause can be listed in any order.
• Each value must be type compatible with its associated column.
• The values must comply with any constraints in the schema, such as unique secondary indexes or non-nullable columns.
• Updates with joins are not supported.
• You cannot update primary key columns.

If a statement does not comply with the rules, Spanner raises an error and the entire statement fails.

Columns not included in the SET clause are not modified.

Column updates are performed simultaneously. For example, you can swap two column values using a single SET clause:

  SET 
  
 x 
  
 = 
  
 y 
 , 
  
 y 
  
 = 
  
 x 
 

Value type compatibility

Values updated with an UPDATE statement must be compatible with the target column's type. A value's type is compatible with the target column's type if the value meets one of the following criteria:

• The value type matches the column type exactly. For example, the value type is INT64 and the column type is INT64 .
• GoogleSQL can implicitly coerce the value into the target type.

Default values

The DEFAULT keyword sets the value of a column to its default value. If the column has no defined default value, the DEFAULT keyword sets it to NULL .

The use of default values is subject to current Spanner limits, including the mutation limit. If a column has a default value and it is used in an insert or update, the column is counted as one mutation. For example, assume that in table T , col_a has a default value. The following updates each result in two mutations. One comes from the primary key, and another comes from either the explicit value (1000) or the default value.

  UPDATE 
  
 T 
  
 SET 
  
 col_a 
  
 = 
  
 1000 
  
 WHERE 
  
 id 
 = 
 1 
 ; 
 UPDATE 
  
 T 
  
 SET 
  
 col_a 
  
 = 
  
 DEFAULT 
  
 WHERE 
  
 id 
 = 
 3 
 ; 
 

For more information about default column values, see the DEFAULT ( expression ) clause in CREATE TABLE .

For more information about mutations, see What are mutations? .

WHERE clause

The WHERE clause is required. This requirement can help prevent accidentally updating all the rows in a table. To update all rows in a table, set the condition to true .

The WHERE clause can contain any valid SQL boolean expression, including a subquery that refers to other tables.

THEN RETURN

With the optional THEN RETURN clause, you can obtain data from rows that are being updated in a table. To learn more about the values that you can use in this clause, see THEN RETURN .

Aliases

The WHERE clause has an implicit alias to table_name . This alias lets you reference columns in table_name without qualifying them with table_name . For example, if your statement starts with UPDATE Singers , then you can access any columns of Singers in the WHERE clause. In this example, FirstName and LastName are columns in the Singers table:

  UPDATE 
  
 Singers 
 SET 
  
 BirthDate 
  
 = 
  
 '1990-10-10' 
 WHERE 
  
 FirstName 
  
 = 
  
 'Marc' 
  
 AND 
  
 LastName 
  
 = 
  
 'Richards' 
 ; 
 

You can also create an explicit alias using the optional AS keyword. For more details on aliases, see Query syntax .

UPDATE examples

UPDATE with literal values example

The following example updates the Singers table by updating the BirthDate column in one of the rows.

  UPDATE 
  
 Singers 
 SET 
  
 BirthDate 
  
 = 
  
 '1990-10-10' 
 , 
  
 SingerInfo 
  
 = 
  
 "nationality:'USA'" 
 WHERE 
  
 FirstName 
  
 = 
  
 'Marc' 
  
 AND 
  
 LastName 
  
 = 
  
 'Richards' 
 ; 
 

The following table shows the data before the statement is executed.

The following table shows the data after the statement is executed.

UPDATE ARRAY columns example

The following example updates an ARRAY column.

  UPDATE 
  
 Concerts 
  
 SET 
  
 TicketPrices 
  
 = 
  
 [ 
 25 
 , 
  
 50 
 , 
  
 100 
 ] 
  
 WHERE 
  
 VenueId 
  
 = 
  
 1 
 ; 
 

The following table shows the data before the statement is executed.

The following table shows the data after the statement is executed.

UPDATE with THEN RETURN example

The following query updates all rows where the singer first name is equal to Russell and returns the SingerId in the updated rows. It also extracts the year from the updated BirthDate column as a new output column called year .

  UPDATE 
  
 Singers 
 SET 
  
 BirthDate 
  
 = 
  
 '1990-10-10' 
 WHERE 
  
 FirstName 
  
 = 
  
 'Russell' 
 THEN 
  
 RETURN 
  
 SingerId 
 , 
  
 EXTRACT 
 ( 
 YEAR 
  
 FROM 
  
 BirthDate 
 ) 
  
 AS 
  
 year 
 ; 
 

The following table shows the query result:

Bound STRUCT parameters

You can use bound STRUCT parameters in the WHERE clause of a DML statement. The following code example updates the LastName in rows filtered by FirstName and LastName .

  using 
  
  Google.Cloud.Spanner.Data 
 
 ; 
 using 
  
 System 
 ; 
 using 
  
 System.Threading.Tasks 
 ; 
 public 
  
 class 
  
 UpdateUsingDmlWithStructCoreAsyncSample 
 { 
  
 public 
  
 async 
  
 Task<int> 
  
 UpdateUsingDmlWithStructCoreAsync 
 ( 
 string 
  
 projectId 
 , 
  
 string 
  
 instanceId 
 , 
  
 string 
  
 databaseId 
 ) 
  
 { 
  
 var 
  
 nameStruct 
  
 = 
  
 new 
  
  SpannerStruct 
 
  
 { 
  
 { 
  
 "FirstName" 
 , 
  
  SpannerDbType 
 
 . 
  String 
 
 , 
  
 "Timothy" 
  
 }, 
  
 { 
  
 "LastName" 
 , 
  
  SpannerDbType 
 
 . 
  String 
 
 , 
  
 "Campbell" 
  
 } 
  
 }; 
  
 string 
  
 connectionString 
  
 = 
  
 $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}" 
 ; 
  
 using 
  
 var 
  
 connection 
  
 = 
  
 new 
  
  SpannerConnection 
 
 ( 
 connectionString 
 ); 
  
 await 
  
 connection 
 . 
  OpenAsync 
 
 (); 
  
 using 
  
 var 
  
 cmd 
  
 = 
  
 connection 
 . 
  CreateDmlCommand 
 
 ( 
 "UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name" 
 ); 
  
 cmd 
 . 
  Parameters 
 
 . 
 Add 
 ( 
 "name" 
 , 
  
 nameStruct 
 . 
  GetSpannerDbType 
 
 (), 
  
 nameStruct 
 ); 
  
 int 
  
 rowCount 
  
 = 
  
 await 
  
 cmd 
 . 
 ExecuteNonQueryAsync 
 (); 
  
 Console 
 . 
 WriteLine 
 ( 
 $"{rowCount} row(s) updated..." 
 ); 
  
 return 
  
 rowCount 
 ; 
  
 } 
 } 
 

  import 
  
 ( 
  
 "context" 
  
 "fmt" 
  
 "io" 
  
 "cloud.google.com/go/spanner" 
 ) 
 func 
  
 updateUsingDMLStruct 
 ( 
 w 
  
 io 
 . 
 Writer 
 , 
  
 db 
  
 string 
 ) 
  
 error 
  
 { 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 spanner 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 db 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 _ 
 , 
  
 err 
  
 = 
  
 client 
 . 
 ReadWriteTransaction 
 ( 
 ctx 
 , 
  
 func 
 ( 
 ctx 
  
 context 
 . 
 Context 
 , 
  
 txn 
  
 * 
 spanner 
 . 
 ReadWriteTransaction 
 ) 
  
 error 
  
 { 
  
 type 
  
 name 
  
 struct 
  
 { 
  
 FirstName 
  
 string 
  
 LastName 
  
 string 
  
 } 
  
 var 
  
 singerInfo 
  
 = 
  
 name 
 { 
 "Timothy" 
 , 
  
 "Campbell" 
 } 
  
 stmt 
  
 := 
  
 spanner 
 . 
  Statement 
 
 { 
  
 SQL 
 : 
  
 `Update Singers Set LastName = 'Grant' 
 WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name` 
 , 
  
 Params 
 : 
  
 map 
 [ 
 string 
 ] 
 interface 
 {}{ 
 "name" 
 : 
  
 singerInfo 
 }, 
  
 } 
  
 rowCount 
 , 
  
 err 
  
 := 
  
 txn 
 . 
 Update 
 ( 
 ctx 
 , 
  
 stmt 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 fmt 
 . 
 Fprintf 
 ( 
 w 
 , 
  
 "%d record(s) inserted.\n" 
 , 
  
 rowCount 
 ) 
  
 return 
  
 nil 
  
 }) 
  
 return 
  
 err 
 } 
 

  static 
  
 void 
  
 updateUsingDmlWithStruct 
 ( 
 DatabaseClient 
  
 dbClient 
 ) 
  
 { 
  
 Struct 
  
 name 
  
 = 
  
 Struct 
 . 
 newBuilder 
 (). 
 set 
 ( 
 "FirstName" 
 ). 
 to 
 ( 
 "Timothy" 
 ). 
 set 
 ( 
 "LastName" 
 ). 
 to 
 ( 
 "Campbell" 
 ). 
 build 
 (); 
  
 Statement 
  
 s 
  
 = 
  
 Statement 
 . 
 newBuilder 
 ( 
  
 "UPDATE Singers SET LastName = 'Grant' " 
  
 + 
  
 "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) " 
  
 + 
  
 "= @name" 
 ) 
  
 . 
 bind 
 ( 
 "name" 
 ) 
  
 . 
 to 
 ( 
 name 
 ) 
  
 . 
 build 
 (); 
  
 dbClient 
  
 . 
 readWriteTransaction 
 () 
  
 . 
 run 
 ( 
 transaction 
  
 - 
>  
 { 
  
 long 
  
 rowCount 
  
 = 
  
 transaction 
 . 
 executeUpdate 
 ( 
 s 
 ); 
  
 System 
 . 
 out 
 . 
 printf 
 ( 
 "%d record updated.\n" 
 , 
  
 rowCount 
 ); 
  
 return 
  
 null 
 ; 
  
 }); 
 } 
 

  // Imports the Google Cloud client library 
 const 
  
 { 
 Spanner 
 } 
  
 = 
  
 require 
 ( 
 ' @google-cloud/spanner 
' 
 ); 
 const 
  
 nameStruct 
  
 = 
  
  Spanner 
 
 . 
  struct 
 
 ({ 
  
 FirstName 
 : 
  
 'Timothy' 
 , 
  
 LastName 
 : 
  
 'Campbell' 
 , 
 }); 
 /** 
 * TODO(developer): Uncomment the following lines before running the sample. 
 */ 
 // const projectId = 'my-project-id'; 
 // const instanceId = 'my-instance'; 
 // const databaseId = 'my-database'; 
 // Creates a client 
 const 
  
 spanner 
  
 = 
  
 new 
  
  Spanner 
 
 ({ 
  
 projectId 
 : 
  
 projectId 
 , 
 }); 
 // Gets a reference to a Cloud Spanner instance and database 
 const 
  
 instance 
  
 = 
  
 spanner 
 . 
 instance 
 ( 
 instanceId 
 ); 
 const 
  
 database 
  
 = 
  
 instance 
 . 
 database 
 ( 
 databaseId 
 ); 
 database 
 . 
  runTransaction 
 
 ( 
 async 
  
 ( 
 err 
 , 
  
 transaction 
 ) 
  
 = 
>  
 { 
  
 if 
  
 ( 
 err 
 ) 
  
 { 
  
 console 
 . 
 error 
 ( 
 err 
 ); 
  
 return 
 ; 
  
 } 
  
 try 
  
 { 
  
 const 
  
 [ 
 rowCount 
 ] 
  
 = 
  
 await 
  
 transaction 
 . 
  runUpdate 
 
 ({ 
  
 sql 
 : 
  
 `UPDATE Singers SET LastName = 'Grant' 
 WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name` 
 , 
  
 params 
 : 
  
 { 
  
 name 
 : 
  
 nameStruct 
 , 
  
 }, 
  
 }); 
  
 console 
 . 
 log 
 ( 
 `Successfully updated 
 ${ 
 rowCount 
 } 
 record.` 
 ); 
  
 await 
  
 transaction 
 . 
 commit 
 (); 
  
 } 
  
 catch 
  
 ( 
 err 
 ) 
  
 { 
  
 console 
 . 
 error 
 ( 
 'ERROR:' 
 , 
  
 err 
 ); 
  
 } 
  
 finally 
  
 { 
  
 // Close the database when finished. 
  
 database 
 . 
 close 
 (); 
  
 } 
 }); 
 

  use Google\Cloud\Spanner\SpannerClient; 
 use Google\Cloud\Spanner\Database; 
 use Google\Cloud\Spanner\Transaction; 
 use Google\Cloud\Spanner\StructType; 
 use Google\Cloud\Spanner\StructValue; 
 /** 
 * Update data with a DML statement using Structs. 
 * 
 * The database and table must already exist and can be created using 
 * `create_database`. 
 * Example: 
 * ``` 
 * insert_data($instanceId, $databaseId); 
 * ``` 
 * 
 * @param string $instanceId The Spanner instance ID. 
 * @param string $databaseId The Spanner database ID. 
 */ 
 function update_data_with_dml_structs(string $instanceId, string $databaseId): void 
 { 
 $spanner = new SpannerClient(); 
 $instance = $spanner->instance($instanceId); 
 $database = $instance->database($databaseId); 
 $database->runTransaction(function (Transaction $t) { 
 $nameValue = (new StructValue) 
 ->add('FirstName', 'Timothy') 
 ->add('LastName', 'Campbell'); 
 $nameType = (new StructType) 
 ->add('FirstName', Database::TYPE_STRING) 
 ->add('LastName', Database::TYPE_STRING); 
 $rowCount = $t->executeUpdate( 
 "UPDATE Singers SET LastName = 'Grant' " 
 . 'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) ' 
 . '= @name', 
 [ 
 'parameters' => [ 
 'name' => $nameValue 
 ], 
 'types' => [ 
 'name' => $nameType 
 ] 
 ]); 
 $t->commit(); 
 printf('Updated %d row(s).' . PHP_EOL, $rowCount); 
 }); 
 } 
 

  # instance_id = "your-spanner-instance" 
 # database_id = "your-spanner-db-id" 
 spanner_client 
 = 
 spanner 
 . 
 Client 
 () 
 instance 
 = 
 spanner_client 
 . 
 instance 
 ( 
 instance_id 
 ) 
 database 
 = 
 instance 
 . 
 database 
 ( 
 database_id 
 ) 
 record_type 
 = 
 param_types 
 . 
 Struct 
 ( 
 [ 
 param_types 
 . 
 StructField 
 ( 
 "FirstName" 
 , 
 param_types 
 . 
 STRING 
 ), 
 param_types 
 . 
 StructField 
 ( 
 "LastName" 
 , 
 param_types 
 . 
 STRING 
 ), 
 ] 
 ) 
 record_value 
 = 
 ( 
 "Timothy" 
 , 
 "Campbell" 
 ) 
 def 
  
 write_with_struct 
 ( 
 transaction 
 ): 
 row_ct 
 = 
 transaction 
 . 
 execute_update 
 ( 
 "UPDATE Singers SET LastName = 'Grant' " 
 "WHERE STRUCT<FirstName STRING, LastName STRING>" 
 "(FirstName, LastName) = @name" 
 , 
 params 
 = 
 { 
 "name" 
 : 
 record_value 
 }, 
 param_types 
 = 
 { 
 "name" 
 : 
 record_type 
 }, 
 ) 
 print 
 ( 
 " 
 {} 
 record(s) updated." 
 . 
 format 
 ( 
 row_ct 
 )) 
 database 
 . 
 run_in_transaction 
 ( 
 write_with_struct 
 ) 
 

  # project_id  = "Your Google Cloud project ID" 
 # instance_id = "Your Spanner instance ID" 
 # database_id = "Your Spanner database ID" 
 require 
  
 "google/cloud/spanner" 
 spanner 
  
 = 
  
 Google 
 :: 
 Cloud 
 :: 
 Spanner 
 . 
 new 
  
 project 
 : 
  
 project_id 
 client 
  
 = 
  
 spanner 
 . 
 client 
  
 instance_id 
 , 
  
 database_id 
 row_count 
  
 = 
  
 0 
 name_struct 
  
 = 
  
 { 
  
 FirstName 
 : 
  
 "Timothy" 
 , 
  
 LastName 
 : 
  
 "Campbell" 
  
 } 
 client 
 . 
 transaction 
  
 do 
  
 | 
 transaction 
 | 
  
 row_count 
  
 = 
  
 transaction 
 . 
 execute_update 
 ( 
  
 "UPDATE Singers SET LastName = 'Grant' 
 WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name" 
 , 
  
 params 
 : 
  
 { 
  
 name 
 : 
  
 name_struct 
  
 } 
  
 ) 
 end 
 puts 
  
 " 
 #{ 
 row_count 
 } 
 record updated." 
 

Commit timestamps

Use the PENDING_COMMIT_TIMESTAMP function to write commit timestamps to a TIMESTAMP column. The column must have the allow_commit_timestamp option set to true . The following DML statement updates the LastUpdated column in the Singers table with the commit timestamp:

  UPDATE 
  
 Singers 
  
 SET 
  
 LastUpdated 
  
 = 
  
 PENDING_COMMIT_TIMESTAMP 
 () 
  
 WHERE 
  
 SingerId 
  
 = 
  
 1 
 ; 
 

For more information on using commit timestamps in DML, see Commit timestamps in GoogleSQL-dialect databases and Commit timestamps in PostgreSQL-dialect databases .

Update fields in protocol buffers

You can update non-repeating and repeating fields in protocol buffers. Consider the Singers example table . It contains a column, AlbumInfo , of type Albums , and the Albums column contains a non-repeating field tracks .

The following statement updates the value of tracks :

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 s 
 . 
 AlbumInfo 
 . 
 tracks 
  
 = 
  
 15 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 "Fire is hot" 
 ; 
 

You can also update a repeated field using an array of values:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 s 
 . 
 AlbumInfo 
 . 
 comments 
  
 = 
  
 [ 
 "A good album!" 
 , 
  
 "Hurt my ears!" 
 , 
  
 "Totally unlistenable." 
 ] 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 "Fire is Hot" 
 ; 
 

Nested updates

You can construct DML statements inside a parent update statement that modify a repeated field of a protocol buffer or an array. These statements are called nested updates .

For example, the Album message contains a repeated field called comments . This nested update statement adds a comment to an album:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 INSERT 
  
 s 
 . 
 AlbumInfo 
 . 
 comments 
  
 VALUES 
  
 ( 
 "Groovy!" 
 )) 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 "Fire is Hot" 
 ; 
 

Album also contains a repeated protocol buffer, Song , which provides information about a song on the album. This nested update statement updates the album with a new song:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 INSERT 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
 ( 
 Song 
 ) 
  
 VALUES 
  
 ( 
 "songtitle: 'Bonus Track', length: 180" 
 )) 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 "Fire is Hot" 
 ; 
 

If the repeated field is another protocol buffer, you can provide the protocol buffer as a string literal. For example, the following statement adds a new song to the album and updates the number of tracks.

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 INSERT 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 VALUES 
  
 ( 
 '''songtitle: ' 
 Bonus 
  
 Track 
 ', length:180''' 
 )), 
  
 s 
 . 
 Albums 
 . 
 tracks 
  
 = 
  
 16 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
  
 and 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 "Fire is Hot" 
 ; 
 

You can also nest a nested update statement in another nested update statement. For example, the Song protocol buffer itself has another repeated protocol buffer, Chart , which provides information on what chart the song appears on, and what rank it has.

The following statement adds a new chart to a song:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 UPDATE 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 so 
  
 SET 
  
 ( 
 INSERT 
  
 INTO 
  
 so 
 . 
 Chart 
  
 VALUES 
  
 ( 
 "chartname: 'Galaxy Top 100', rank: 5" 
 )) 
  
 WHERE 
  
 so 
 . 
 songtitle 
  
 = 
  
 "Bonus Track" 
 ) 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
 ; 
 

This following statement updates the chart to reflect a new rank for the song:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 UPDATE 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 so 
  
 SET 
  
 ( 
 UPDATE 
  
 so 
 . 
 Chart 
  
 c 
  
 SET 
  
 c 
 . 
 rank 
  
 = 
  
 2 
  
 WHERE 
  
 c 
 . 
 chartname 
  
 = 
  
 "Galaxy Top 100" 
 ) 
  
 WHERE 
  
 so 
 . 
 songtitle 
  
 = 
  
 "Bonus Track" 
 ) 
 WHERE 
  
 s 
 . 
 SingerId 
  
 = 
  
 5 
 ; 
 

GoogleSQL treats an array or repeated field inside a row that matches an UPDATE WHERE clause as a table, with individual elements of the array or field treated like rows. These rows can then have nested DML statements run against them, allowing you to delete, update, and insert data as needed.

Modify multiple fields

The previous sections demonstrates how to update a single value in a compound data type. You can also modify multiple fields in a compound data type within a single statement. For example:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 DELETE 
  
 FROM 
  
 s 
 . 
 SingerInfo 
 . 
 Residence 
  
 r 
  
 WHERE 
  
 r 
 . 
 City 
  
 = 
  
 'Seattle' 
 ), 
  
 ( 
 UPDATE 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 song 
  
 SET 
  
 song 
 . 
 songtitle 
  
 = 
  
 'No, This Is Rubbish' 
  
 WHERE 
  
 song 
 . 
 songtitle 
  
 = 
  
 'This Is Pretty Good' 
 ), 
  
 ( 
 INSERT 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 VALUES 
  
 ( 
 "songtitle: 'The Second Best Song'" 
 )) 
 WHERE 
  
 SingerId 
  
 = 
  
 3 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 'Go! Go! Go!' 
 ; 
 

Nested queries are processed as follows:

1. Delete all rows that match a WHERE clause of a DELETE statement.
2. Update any remaining rows that match a WHERE clause of an UPDATE statement. Each row must match at most one UPDATE WHERE clause, or the statement fails due to overlapping updates.
3. Insert all rows in INSERT statements.

You must construct nested statements that affect the same field in the following order:

• DELETE
• UPDATE
• INSERT

For example:

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 DELETE 
  
 FROM 
  
 s 
 . 
 SingerInfo 
 . 
 Residence 
  
 r 
  
 WHERE 
  
 r 
 . 
 City 
  
 = 
  
 'Seattle' 
 ), 
  
 ( 
 UPDATE 
  
 s 
 . 
 SingerInfo 
 . 
 Residence 
  
 r 
  
 SET 
  
 r 
 . 
 end_year 
  
 = 
  
 2015 
  
 WHERE 
  
 r 
 . 
 City 
  
 = 
  
 'Eugene' 
 ), 
  
 ( 
 INSERT 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 VALUES 
  
 ( 
 "songtitle: 'The Second Best Song'" 
 )) 
 WHERE 
  
 SingerId 
  
 = 
  
 3 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 'Go! Go! Go!' 
 ; 
 

The following statement is invalid, because the UPDATE statement happens after the INSERT statement.

  UPDATE 
  
 Singers 
  
 s 
 SET 
  
 ( 
 DELETE 
  
 FROM 
  
 s 
 . 
 SingerInfo 
 . 
 Residence 
  
 r 
  
 WHERE 
  
 r 
 . 
 City 
  
 = 
  
 'Seattle' 
 ), 
  
 ( 
 INSERT 
  
 s 
 . 
 AlbumInfo 
 . 
 Song 
  
 VALUES 
  
 ( 
 "songtitle: 'The Second Best Song'" 
 )), 
  
 ( 
 UPDATE 
  
 s 
 . 
 SingerInfo 
 . 
 Residence 
  
 r 
  
 SET 
  
 r 
 . 
 end_year 
  
 = 
  
 2015 
  
 WHERE 
  
 r 
 . 
 City 
  
 = 
  
 'Eugene' 
 ) 
 WHERE 
  
 SingerId 
  
 = 
  
 3 
  
 AND 
  
 s 
 . 
 AlbumInfo 
 . 
 title 
  
 = 
  
 'Go! Go! Go!' 
 ; 
 

In nested queries, you can't use INSERT OR REPLACE statements. These types of statements don't work because arrays and other compound data types don't always have a primary key, so there is no applicable definition of duplicate rows.