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

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

Tables used in examples

CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),BirthDateDATE,StatusSTRING(1024),LastUpdatedTIMESTAMP,SingerInfogooglesql.example.SingerInfo,AlbumInfogooglesql.example.Album,)PRIMARYKEY(SingerId);CREATETABLEAlbumInfo(SingerIdINT64NOTNULL,AlbumIdINT64NOTNULL,AlbumTitleSTRING(MAX),MarketingBudgetINT64,)PRIMARYKEY(SingerId,AlbumId),INTERLEAVEINPARENTSingersONDELETECASCADE;CREATETABLESongs(SingerIdINT64NOTNULL,AlbumIdINT64NOTNULL,TrackIdINT64NOTNULL,SongNameSTRING(MAX),DurationINT64,SongGenreSTRING(25),)PRIMARYKEY(SingerId,AlbumId,TrackId),INTERLEAVEINPARENTAlbumInfoONDELETECASCADE;CREATETABLEConcerts(VenueIdINT64NOTNULL,SingerIdINT64NOTNULL,ConcertDateDATENOTNULL,BeginTimeTIMESTAMP,EndTimeTIMESTAMP,TicketPricesARRAY<INT64>,)PRIMARYKEY(VenueId,SingerId,ConcertDate);CREATETABLEAckworthSingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),BirthDateDATE,)PRIMARYKEY(SingerId);CREATETABLEFans(FanIdSTRING(36)DEFAULT(GENERATE_UUID()),FirstNameSTRING(1024),LastNameSTRING(1024),)PRIMARYKEY(FanId);

Definitions for protocol buffers used in examples

packagegooglesql.example;messageSingerInfo{optionalstringnationality=1;repeatedResidenceresidence=2;messageResidence{requiredint64start_year=1;optionalint64end_year=2;optionalstringcity=3;optionalstringcountry=4;}}messageAlbum{optionalstringtitle=1;optionalint64tracks=2;repeatedstringcomments=3;repeatedSongsong=4;messageSong{optionalstringsongtitle=1;optionalint64length=2;repeatedChartchart=3;messageChart{optionalstringchartname=1;optionalint64rank=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 repeatin 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 asINSERT, are keywords.

INSERT statement

Use theINSERT statement to add new rows to a table. TheINSERT statementcan insert one or more rows specified by value expressions, or zero or more rowsproduced by a query. The statement by default returns the number of rowsinserted 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_queryexpr:value_expression|DEFAULTreturn_clause:THENRETURN[WITHACTION[ASalias]]{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 theVALUES clause or the select querypositionally 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 valuespecified.

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

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

Value type compatibility

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

• The value type matches the column type exactly. For example, inserting avalue of typeINT64 in a column that has a type ofINT64 is compatible.
• GoogleSQL canimplicitly coercethe value into the target type.

Default values

Use theDEFAULT keyword to insert the default value of a column.If a column is not included in the list, GoogleSQL assigns the defaultvalue of the column. If the column has no defineddefault 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 inan insert or update, the column is counted as one mutation. For example,assuming that tableT has three columns and thatcol_a has a default value,the following inserts each result in three mutations:

INSERTINTOT(id,col_a,col_b)VALUES(1,DEFAULT,1);INSERTINTOT(id,col_a,col_b)VALUES(2,200,2);INSERTINTOT(id,col_b)VALUES(3,3);

For more information about default column values, see theDEFAULT (expression )clause inCREATE TABLE.

For more information about mutations, seeWhat are mutations?.

INSERT OR IGNORE

Use theINSERT OR IGNORE clause to insert new rows that don'texist in the table. If the primary key of the row already exists, then the rowis ignored. For anINSERT OR IGNORE query that inserts multiple rows orinserts from a subquery, only the new rows are inserted. Rows where the primarykey already exists are ignored.

For example, if the primary key isSingerId and the table already containsaSingerId of 7, then in the following example,INSERT would insert thefirst row and ignore the second row:

INSERTORIGNOREINTOSingers(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 useINSERT OR IGNORE in single or batch DML requests using theexecuteBatchDmlAPI.

INSERT OR UPDATE

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

For example, in the following statement,INSERT OR UPDATE modifies the columnvalue ofStatus fromactive toinactive in the existing table with theprimary keySingerId of5.

INSERTORUPDATEINTOSingers(SingerId,Status)VALUES(5,"inactive");

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

You can useINSERT OR UPDATE in single or batch DML requests using theexecuteBatchDmlAPI.

THEN RETURN

Use theTHEN RETURN clause to return the results of theINSERT operation andselected data from the newly inserted rows. This clause is especially useful forretrieving values of columns with default values, generated columns, andauto-generated keys, without having to use additionalSELECT statements.

Use theTHEN RETURN clause to capture expressions based on newly inserted rowsthat include the following:

• WITH ACTION: An optional clause that adds a string column calledACTION tothe result row set. Each value in this column represents the type of actionthat was applied to the column during statement execution. Values includeINSERT,DELETE, andUPDATE. TheACTION column is appended as the lastoutput column.
• *: Returns all columns.
• table_name.*: Returns all columns from the table.You cannot use the .* expression with other expressions, including fieldaccess.
• EXCEPT ( column_name [, ...] ): Specifies the columns to exclude from theresult. All matching column names are omitted from the output.
• REPLACE ( expression [ AS ] column_name [, ...] ): Specifies one or moreexpression AS identifier clauses. Each identifier must match a column namefrom thetable_name.* statement. In the output column list, the column thatmatches the identifier in aREPLACE clause is replaced by the expression inthatREPLACE clause.Note that the value that gets inserted into the table is not replaced, justthe value returned by theTHEN RETURN clause.
• expression: Represents a column name of the table specified bytable_nameor an expression that uses any combination of such column names. Column names arevalid if they belong to columns of thetable_name. Excluded expressionsinclude aggregate and analytic functions.
• alias: Represents a temporary name for an expression in the query.

For instructions and code samples, seeModify data with the returning DMLstatements.

INSERT examples

INSERT using literal values examples

The following example adds two rows to theSingers table.

INSERTINTOSingers(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 anothertable using aSELECT statement as the input:

INSERTINTOSingers(SingerId,FirstName,LastName)SELECTSingerId,FirstName,LastNameFROMAckworthSingers;

If theSingers table had no rows, and theAckworthSingers table had threerows, then there are now three rows in theSingers table:

The following example shows how to useUNNEST to return a table that is theinput to theINSERT command.

INSERTINTOSingers(SingerId,FirstName,LastName)SELECT*FROMUNNEST([(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 theSingers table:

INSERT using a subquery example

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

INSERTINTOSingers(SingerId,FirstName)VALUES(4,(SELECTFirstNameFROMAckworthSingersWHERESingerId=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:

INSERTINTOSingers(SingerId,FirstName,LastName)VALUES(4,(SELECTFirstNameFROMAckworthSingersWHERESingerId=4),(SELECTLastNameFROMAckworthSingersWHERESingerId=4));

INSERT with THEN RETURN examples

The following query inserts two rows into a table, usesTHEN RETURN to fetchthe SingerId column from these rows, and computes a new column calledFullName.

INSERTINTOSingers(SingerId,FirstName,LastName)VALUES(7,'Melissa','Garcia'),(8,'Russell','Morales')THENRETURNSingerId,FirstName||' '||LastNameASFullName;

The following table shows the query result:

The following query inserts a row to theFans table. Spannerautomatically generates a Version 4 UUID for the primary keyFanId, andreturns it using theTHEN RETURN clause.

INSERTINTOFans(FirstName,LastName)VALUES('Melissa','Garcia')THENRETURNFanId;

The following table shows the query result:

The following query tries to insert or update a row into a table. It usesTHEN RETURN to fetch the modified row andWITH ACTION to show the modifiedrow action type.

INSERTORUPDATESingers(SingerId,FirstName,LastName)VALUES(7,'Melissa','Gartner')THENRETURNWITHACTIONSingerId,FirstName||' '||LastNameASFullName;

INSERT OR IGNORE example

The following query inserts a row in theSingers table for singers with an IDbetween 10 and 100. If an ID already exists inSingers, it'signored.

INSERTORIGNOREINTOSingers(SingerId,FirstName,LastName,BirthDate,Status,SingerInfo)(SELECTid,fname,lname,dob,status,infoFROMlatest_albumWHEREid >10ANDid <100);

DELETE statement

Use theDELETE statement to delete rows from a table.

[statement_hint_expr]DELETE[FROM]table_name[table_hint_expr][[AS]alias]WHEREcondition[return_clause];statement_hint_expr:'@{'statement_hint_key=statement_hint_value'}'table_hint_expr:'@{'table_hint_key=table_hint_value'}'return_clause:THENRETURN[WITHACTION[ASalias]]{select_all|expression[[AS]alias]}[,...]select_all:[table_name.]*[EXCEPT(column_name[,...])][REPLACE(expression[AS]column_name[,...])]

WHERE clause

TheWHERE clause is required. This requirement can help prevent accidentallydeleting all the rows in a table. To delete all rows in a table, set thecondition totrue:

DELETEFROMtable_nameWHEREtrue;

TheWHERE clause can contain any valid SQL statement, including a subquerythat refers to other tables.

Aliases

TheWHERE clause has an implicit alias totable_name. This alias lets youreference columns intable_name without qualifying them withtable_name. For example, if your statement started withDELETE FROMSingers, then you could access any columns ofSingers in theWHERE clause.In this example,FirstName is a column in theSingers table:

DELETEFROMSingersWHEREFirstName='Alice';

You can also create an explicit alias using the optionalAS keyword. For moredetails on aliases, seeQuery syntax.

THEN RETURN

With the optionalTHEN RETURN clause, you can obtain data from rows that arebeing deleted in a table. To learn more about the values that you can use inthis clause, seeTHEN 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 followingDELETE statement deletes all singers whose first name isAlice.

DELETEFROMSingersWHEREFirstName='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 inSINGERS whose first name isnot inAckworthSingers.

DELETEFROMSingersWHEREFirstNameNOTIN(SELECTFirstNamefromAckworthSingers);

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 thatcontains a singer calledMelissa and returns all columns in the deleted rowsexcept theLastUpdated column.

DELETEFROMSingersWHEREFirstname='Melissa'THENRETURN*EXCEPT(LastUpdated);

The following table shows the query result:

UPDATE statement

Use theUPDATE statement to update existing rows in a table.

[statement_hint_expr]UPDATEtable_name[table_hint_expr][[AS]alias]SETupdate_item[,...]WHEREcondition[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:THENRETURN[WITHACTION[ASalias]]{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.
• TheSET clause is a list of update_items to perform on each row where theWHERE condition is true.
• expression is an update expression. The expression can be a literal, a SQLexpression, or a SQL subquery.

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

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

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

  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 theSET clause.
• The columns in theSET 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 asunique 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 errorand the entire statement fails.

Columns not included in theSET clause are not modified.

Column updates are performed simultaneously. For example, you can swap twocolumn values using a singleSET clause:

SETx=y,y=x

Value type compatibility

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

• The value type matches the column type exactly. For example, the value typeisINT64 and the column type isINT64.
• GoogleSQL canimplicitly coercethe value into the target type.

Default values

TheDEFAULT keyword sets the value of a column to its default value. If thecolumn has no defined default value, theDEFAULT keyword sets it toNULL.

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 tableT,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.

UPDATETSETcol_a=1000WHEREid=1;UPDATETSETcol_a=DEFAULTWHEREid=3;

For more information about default column values, see theDEFAULT (expression )clause inCREATE TABLE.

For more information about mutations, seeWhat are mutations?.

WHERE clause

TheWHERE clause is required. This requirement can help prevent accidentallyupdating all the rows in a table. To update all rows in a table, set thecondition totrue.

TheWHERE clause can contain any valid SQL boolean expression, including asubquery that refers to other tables.

THEN RETURN

With the optionalTHEN RETURN clause, you can obtain data from rows that arebeing updated in a table. To learn more about the values that you can use inthis clause, seeTHEN RETURN.

Aliases

TheWHERE clause has an implicit alias totable_name. This alias lets youreference columns intable_name without qualifying them withtable_name. For example, if your statement starts withUPDATE Singers, thenyou can access any columns ofSingers in theWHERE clause. In this example,FirstName andLastName are columns in theSingers table:

UPDATESingersSETBirthDate='1990-10-10'WHEREFirstName='Marc'ANDLastName='Richards';

You can also create an explicit alias using the optionalAS keyword. For moredetails on aliases, seeQuery syntax.

UPDATE examples

UPDATE with literal values example

The following example updates theSingers table by updating theBirthDatecolumn in one of the rows.

UPDATESingersSETBirthDate='1990-10-10',SingerInfo="nationality:'USA'"WHEREFirstName='Marc'ANDLastName='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 anARRAY column.

UPDATEConcertsSETTicketPrices=[25,50,100]WHEREVenueId=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 toRussell and returns theSingerId in the updated rows. It also extracts theyear from the updatedBirthDate column as a new output column calledyear.

UPDATESingersSETBirthDate='1990-10-10'WHEREFirstName='Russell'THENRETURNSingerId,EXTRACT(YEARFROMBirthDate)ASyear;

The following table shows the query result:

Bound STRUCT parameters

You can useboundSTRUCT parametersin theWHERE clause of a DML statement. The following code example updates theLastName in rows filtered byFirstName andLastName.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassUpdateUsingDmlWithStructCoreAsyncSample{publicasyncTask<int>UpdateUsingDmlWithStructCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){varnameStruct=newSpannerStruct{{"FirstName",SpannerDbType.String,"Timothy"},{"LastName",SpannerDbType.String,"Campbell"}};stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name");cmd.Parameters.Add("name",nameStruct.GetSpannerDbType(),nameStruct);introwCount=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine($"{rowCount} row(s) updated...");returnrowCount;}}

import("context""fmt""io""cloud.google.com/go/spanner")funcupdateUsingDMLStruct(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()_,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{typenamestruct{FirstNamestringLastNamestring}varsingerInfo=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)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) inserted.\n",rowCount)returnnil})returnerr}

staticvoidupdateUsingDmlWithStruct(DatabaseClientdbClient){Structname=Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();Statements=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->{longrowCount=transaction.executeUpdate(s);System.out.printf("%d record updated.\n",rowCount);returnnull;});}

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');constnameStruct=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 clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner instance and databaseconstinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rowCount]=awaittransaction.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.`);awaittransaction.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")defwrite_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.newproject:project_idclient=spanner.clientinstance_id,database_idrow_count=0name_struct={FirstName:"Timothy",LastName:"Campbell"}client.transactiondo|transaction|row_count=transaction.execute_update("UPDATE Singers SET LastName = 'Grant'     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",params:{name:name_struct})endputs"#{row_count} record updated."

Commit timestamps

Use thePENDING_COMMIT_TIMESTAMPfunction to write commit timestamps to aTIMESTAMP column. The column musthave theallow_commit_timestamp option set totrue. The following DMLstatement updates theLastUpdated column in theSingers table with thecommit timestamp:

UPDATESingersSETLastUpdated=PENDING_COMMIT_TIMESTAMP()WHERESingerId=1;

For more information on using commit timestamps in DML, seeCommit timestamps in GoogleSQL-dialect databases andCommit timestamps in PostgreSQL-dialect databases.

Update fields in protocol buffers

You can update non-repeating and repeating fields in protocol buffers.Consider theSingers example table.It contains a column,AlbumInfo, of typeAlbums, and theAlbums columncontains a non-repeating fieldtracks.

The following statement updates the value oftracks:

UPDATESingerssSETs.AlbumInfo.tracks=15WHEREs.SingerId=5ANDs.AlbumInfo.title="Fire is hot";

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

UPDATESingerssSETs.AlbumInfo.comments=["A good album!","Hurt my ears!","Totally unlistenable."]WHEREs.SingerId=5ANDs.AlbumInfo.title="Fire is Hot";

Nested updates

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

For example, theAlbum message contains a repeated field calledcomments.This nested update statement adds a comment to an album:

UPDATESingerssSET(INSERTs.AlbumInfo.commentsVALUES("Groovy!"))WHEREs.SingerId=5ANDs.AlbumInfo.title="Fire is Hot";

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

UPDATESingerssSET(INSERTs.AlbumInfo.Song(Song)VALUES("songtitle: 'Bonus Track', length: 180"))WHEREs.SingerId=5ANDs.AlbumInfo.title="Fire is Hot";

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

UPDATESingerssSET(INSERTs.AlbumInfo.SongVALUES('''songtitle: 'BonusTrack', length:180''')),s.Albums.tracks=16WHEREs.SingerId=5ands.AlbumInfo.title="Fire is Hot";

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

The following statement adds a new chart to a song:

UPDATESingerssSET(UPDATEs.AlbumInfo.SongsoSET(INSERTINTOso.ChartVALUES("chartname: 'Galaxy Top 100', rank: 5"))WHEREso.songtitle="Bonus Track")WHEREs.SingerId=5;

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

UPDATESingerssSET(UPDATEs.AlbumInfo.SongsoSET(UPDATEso.ChartcSETc.rank=2WHEREc.chartname="Galaxy Top 100")WHEREso.songtitle="Bonus Track")WHEREs.SingerId=5;

GoogleSQL treats an array or repeated field inside a row that matchesanUPDATE WHERE clause as a table, with individual elements of the array orfield treated like rows. These rows can then have nested DML statements runagainst 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 acompound data type. You can also modify multiple fields in a compound datatype within a single statement. For example:

UPDATESingerssSET(DELETEFROMs.SingerInfo.ResidencerWHEREr.City='Seattle'),(UPDATEs.AlbumInfo.SongsongSETsong.songtitle='No, This Is Rubbish'WHEREsong.songtitle='This Is Pretty Good'),(INSERTs.AlbumInfo.SongVALUES("songtitle: 'The Second Best Song'"))WHERESingerId=3ANDs.AlbumInfo.title='Go! Go! Go!';

Nested queries are processed as follows:

1. Delete all rows that match aWHERE clause of aDELETE statement.
2. Update any remaining rows that match aWHERE clause of anUPDATEstatement. Each row must match at most oneUPDATE WHERE clause, or thestatement fails due to overlapping updates.
3. Insert all rows inINSERT statements.

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

• DELETE
• UPDATE
• INSERT

For example:

UPDATESingerssSET(DELETEFROMs.SingerInfo.ResidencerWHEREr.City='Seattle'),(UPDATEs.SingerInfo.ResidencerSETr.end_year=2015WHEREr.City='Eugene'),(INSERTs.AlbumInfo.SongVALUES("songtitle: 'The Second Best Song'"))WHERESingerId=3ANDs.AlbumInfo.title='Go! Go! Go!';

The following statement is invalid, because theUPDATE statementhappens after theINSERT statement.

UPDATESingerssSET(DELETEFROMs.SingerInfo.ResidencerWHEREr.City='Seattle'),(INSERTs.AlbumInfo.SongVALUES("songtitle: 'The Second Best Song'")),(UPDATEs.SingerInfo.ResidencerSETr.end_year=2015WHEREr.City='Eugene')WHERESingerId=3ANDs.AlbumInfo.title='Go! Go! Go!';

In nested queries, you can't useINSERT OR REPLACE statements. These typesof statements don't work because arrays and other compound data types don'talways have a primary key, so there is no applicable definition of duplicaterows.