Insert, update, and delete data using data manipulation language (DML)

This page describes how to insert, update, and delete Spannerdata using Data Manipulation Language (DML) statements. You can run DMLstatements using theclient libraries, theGoogle Cloud console, and thegcloudcommand-line tool. You can runPartitioned DMLstatements using the client libraries and thegcloudcommand-line tool.

For the complete DML syntax reference, seeData Manipulation Language syntax for GoogleSQL-dialect databasesorPostgreSQL data manipulation languagefor PostgreSQL-dialect databases

Note: To explore Spanner using a 90-day free trial instance,see Create a Spanner free trial instance.

Use DML

DML supportsINSERT,UPDATE, andDELETE statements in theGoogle Cloud console, Google Cloud CLI, and client libraries.

Locking

You execute DML statements inside read-write transactions. When Spanner reads data, it acquires shared read locks on limited portions of the row ranges that you read. Specifically, it acquires these locks only on the columns you access. The locks can include data that does not satisfy the filter condition of theWHERE clause.

When Spanner modifies data using DML statements, it acquires exclusive locks on the specific data that you are modifying. In addition, it acquires shared locks in the same way as when you read data. If your request includes large row ranges, or an entire table, the shared locks might prevent other transactions from making progress in parallel.

To modify data as efficiently as possible, use aWHERE clause that enables Spanner to read only the necessary rows. You can achieve this goal with a filter on the primary key, or on the key of a secondary index. TheWHERE clause limits the scope of the shared locks and enables Spanner to process the update more efficiently.

For example, suppose that one of the musicians in theSingers table changes their first name, and you need to update the name in your database. You could execute the following DML statement, but it forces Spanner to scan the entire table and acquires shared locks that cover the entire table. As a result, Spanner must read more data than necessary, and concurrent transactions cannot modify the data in parallel:

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN-- IN THE WHERE CLAUSEUPDATESingersSETFirstName="Marcel"WHEREFirstName="Marc"ANDLastName="Richards";

To make the update more efficient, include theSingerId column in theWHERE clause. TheSingerId column is the only primary key column for theSingers table:

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLEUPDATESingersSETFirstName="Marcel"WHEREFirstName="Marc"ANDLastName="Richards"

If there is no index onFirstName orLastName, you need toscan the entire table to find the target singers. If you don't want to add a secondaryindex to make the update more efficient, then include theSingerId columnin theWHERE clause.

TheSingerId column is the only primary key column for theSingers table. To find it, runSELECT in a separate,read-only transaction prior to the update transaction:

SELECTSingerIdFROMSingersWHEREFirstName="Marc"ANDLastName="Richards"--Recommended:IncludingaseekablefilterinthewhereclauseUPDATESingersSETFirstName="Marcel"WHERESingerId=1;

Concurrency

Spanner sequentially executes all the SQL statements (SELECT,INSERT,UPDATE, andDELETE) within a transaction. They are not executedconcurrently. The only exception is that Spanner might executemultipleSELECT statements concurrently, because they are read-only operations.

Transaction limits

A transaction that includes DML statements has thesame limitsas any other transaction. If you have large-scale changes, consider usingPartitioned DML.

If the DML statements in a transaction result in more than80,000 mutations, the DML statement that pushes thetransaction over the limit returns aBadUsage error with a message about toomany mutations.
If the DML statements in a transaction result in a transaction that is largerthan 100 MiB, the DML statement that pushes the transaction overthe limit returns aBadUsage error with a message about thetransaction exceeding the size limit.

Mutations performed using DML are not returned to the client. They are mergedinto the commit request when it is committed, and they count towards the maximumsize limits. Even if the size of the commit request that you send is small, thetransaction might still exceed the allowed size limit.

Run statements in the Google Cloud console

Note: Spanner Studio (formerly labeledQuery in the Google Cloud console)supports SQL, DML, and DDL operations in a single editor. For more information,see Manage your data using the Google Cloud console.

Use the following steps to execute a DML statement in theGoogle Cloud console.

Go to the SpannerInstances page.
Go to the instances page
Select your project in the drop-down list in the toolbar.
Click the name of the instance that contains your database to go to theInstance details page.
In theOverview tab, click the name of your database.TheDatabase details page appears.
ClickSpanner Studio.
Enter a DML statement. For example, the following statement adds a new row totheSingers table.
```
INSERT Singers (SingerId, FirstName, LastName)VALUES (1, 'Marc', 'Richards')
```
ClickRun query.The Google Cloud console displays the result.

Note: When you clickRun query, the transaction commits automatically.

Execute statements with the Google Cloud CLI

To execute DML statements, use thegcloud spanner databases execute-sqlcommand. The following example adds a new row to theSingers table.

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance\--sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"

Modify data using the client library

To execute DML statements using the client library:

Create a read-write transaction.
Call the client library method for DML execution and pass in the DML statement.
Use the return value of the DML execution method to get the number of rowsinserted, updated, or deleted.

The following code example inserts a new row into theSingers table.

C++

You use theExecuteDml() function to execute a DML statement.

voidDmlStandardInsert(google::cloud::spanner::Clientclient){using::google::cloud::StatusOr;namespacespanner=::google::cloud::spanner;std::int64_trows_inserted;autocommit_result=client.Commit([&client,&rows_inserted](spanner::Transactiontxn)->StatusOr<spanner::Mutations>{autoinsert=client.ExecuteDml(std::move(txn),spanner::SqlStatement("INSERT INTO Singers (SingerId, FirstName, LastName)""  VALUES (10, 'Virginia', 'Watson')"));if(!insert)returnstd::move(insert).status();rows_inserted=insert->RowsModified();returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Rows inserted: " <<rows_inserted;std::cout <<"Insert was successful [spanner_dml_standard_insert]\n";}

C#

You use theExecuteNonQueryAsync() method to execute a DML statement.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassInsertUsingDmlCoreAsyncSample{publicasyncTask<int>InsertUsingDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')");introwCount=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine($"{rowCount} row(s) inserted...");returnrowCount;}}

Go

You use theUpdate() method to execute a DML statement.

import("context""fmt""io""cloud.google.com/go/spanner")funcinsertUsingDML(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{stmt:=spanner.Statement{SQL:`INSERT Singers (SingerId, FirstName, LastName)VALUES (10, 'Virginia', 'Watson')`,}rowCount,err:=txn.Update(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) inserted.\n",rowCount)returnnil})returnerr}

Java

You use theexecuteUpdate() method to execute a DML statement.

staticvoidinsertUsingDml(DatabaseClientdbClient){dbClient.readWriteTransaction().run(transaction->{Stringsql="INSERT INTO Singers (SingerId, FirstName, LastName) "+" VALUES (10, 'Virginia', 'Watson')";longrowCount=transaction.executeUpdate(Statement.of(sql));System.out.printf("%d record inserted.\n",rowCount);returnnull;});}

Node.js

You use therunUpdate() method to execute a DML statement.

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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:'INSERT Singers (SingerId, FirstName, LastName) VALUES (10, @firstName, @lastName)',params:{firstName:'Virginia',lastName:'Watson',},});console.log(`Successfully inserted${rowCount} record into the Singers table.`,);awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});

PHP

You use theexecuteUpdate() method to execute a DML statement.

use Google\Cloud\Spanner\SpannerClient;use Google\Cloud\Spanner\Transaction;/** * Inserts sample data into the given database with a DML statement. * * 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 insert_data_with_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $database->runTransaction(function (Transaction $t) {        $rowCount = $t->executeUpdate(            'INSERT Singers (SingerId, FirstName, LastName) '            . " VALUES (10, 'Virginia', 'Watson')");        $t->commit();        printf('Inserted %d row(s).' . PHP_EOL, $rowCount);    });}

Python

You use theexecute_update() method to execute a DML statement.

# 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)definsert_singers(transaction):row_ct=transaction.execute_update("INSERT INTO Singers (SingerId, FirstName, LastName) "" VALUES (10, 'Virginia', 'Watson')")print("{} record(s) inserted.".format(row_ct))database.run_in_transaction(insert_singers)

Ruby

You use theexecute_update() method to execute a DML statement.

# 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=0client.transactiondo|transaction|row_count=transaction.execute_update("INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')")endputs"#{row_count} record inserted."

The following code example updates theMarketingBudget column of theAlbumstable based on aWHERE clause.

C++

voidDmlStandardUpdate(google::cloud::spanner::Clientclient){using::google::cloud::StatusOr;namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit([&client](spanner::Transactiontxn)->StatusOr<spanner::Mutations>{autoupdate=client.ExecuteDml(std::move(txn),spanner::SqlStatement("UPDATE Albums SET MarketingBudget = MarketingBudget * 2""  WHERE SingerId = 1 AND AlbumId = 1"));if(!update)returnstd::move(update).status();returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Update was successful [spanner_dml_standard_update]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassUpdateUsingDmlCoreAsyncSample{publicasyncTask<int>UpdateUsingDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1");introwCount=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine($"{rowCount} row(s) updated...");returnrowCount;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner")funcupdateUsingDML(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{stmt:=spanner.Statement{SQL:`UPDATE AlbumsSET MarketingBudget = MarketingBudget * 2WHERE SingerId = 1 and AlbumId = 1`,}rowCount,err:=txn.Update(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) updated.\n",rowCount)returnnil})returnerr}

Java

staticvoidupdateUsingDml(DatabaseClientdbClient){dbClient.readWriteTransaction().run(transaction->{Stringsql="UPDATE Albums "+"SET MarketingBudget = MarketingBudget * 2 "+"WHERE SingerId = 1 and AlbumId = 1";longrowCount=transaction.executeUpdate(Statement.of(sql));System.out.printf("%d record updated.\n",rowCount);returnnull;});}

Node.js

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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 Albums SET MarketingBudget = MarketingBudget * 2        WHERE SingerId = 1 and AlbumId = 1`,});console.log(`Successfully updated${rowCount} record.`);awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});

PHP

use Google\Cloud\Spanner\SpannerClient;use Google\Cloud\Spanner\Transaction;/** * Updates sample data in the database with a DML statement. * * This requires the `MarketingBudget` column which must be created before * running this sample. You can add the column by running the `add_column` * sample or by running this DDL statement against your database: * *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 * * Example: * ``` * update_data($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function update_data_with_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $database->runTransaction(function (Transaction $t) {        $rowCount = $t->executeUpdate(            'UPDATE Albums '            . 'SET MarketingBudget = MarketingBudget * 2 '            . 'WHERE SingerId = 1 and AlbumId = 1');        $t->commit();        printf('Updated %d row(s).' . PHP_EOL, $rowCount);    });}

Python

# 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)defupdate_albums(transaction):row_ct=transaction.execute_update("UPDATE Albums ""SET MarketingBudget = MarketingBudget * 2 ""WHERE SingerId = 1 and AlbumId = 1")print("{} record(s) updated.".format(row_ct))database.run_in_transaction(update_albums)

Ruby

# 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=0client.transactiondo|transaction|row_count=transaction.execute_update("UPDATE Albums     SET MarketingBudget = MarketingBudget * 2     WHERE SingerId = 1 and AlbumId = 1")endputs"#{row_count} record updated."

The following code example deletes all the rows in theSingers table wheretheFirstName column isAlice.

C++

voidDmlStandardDelete(google::cloud::spanner::Clientclient){using::google::cloud::StatusOr;namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit([&client](spanner::Transactiontxn)->StatusOr<spanner::Mutations>{autodele=client.ExecuteDml(std::move(txn),spanner::SqlStatement("DELETE FROM Singers WHERE FirstName = 'Alice'"));if(!dele)returnstd::move(dele).status();returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Delete was successful [spanner_dml_standard_delete]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassDeleteUsingDmlCoreAsyncSample{publicasyncTask<int>DeleteUsingDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice'");introwCount=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine($"{rowCount} row(s) deleted...");returnrowCount;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner")funcdeleteUsingDML(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{stmt:=spanner.Statement{SQL:`DELETE FROM Singers WHERE FirstName = 'Alice'`}rowCount,err:=txn.Update(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) deleted.\n",rowCount)returnnil})returnerr}

Java

staticvoiddeleteUsingDml(DatabaseClientdbClient){dbClient.readWriteTransaction().run(transaction->{Stringsql="DELETE FROM Singers WHERE FirstName = 'Alice'";longrowCount=transaction.executeUpdate(Statement.of(sql));System.out.printf("%d record deleted.\n",rowCount);returnnull;});}

Node.js

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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:"DELETE FROM Singers WHERE FirstName = 'Alice'",});console.log(`Successfully deleted${rowCount} record.`);awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});

PHP

use Google\Cloud\Spanner\SpannerClient;use Google\Cloud\Spanner\Transaction;/** * Deletes sample data in the database with a DML statement. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function delete_data_with_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $database->runTransaction(function (Transaction $t) {        $rowCount = $t->executeUpdate(            "DELETE FROM Singers WHERE FirstName = 'Alice'");        $t->commit();        printf('Deleted %d row(s).' . PHP_EOL, $rowCount);    });}

Python

# 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)defdelete_singers(transaction):row_ct=transaction.execute_update("DELETE FROM Singers WHERE FirstName = 'Alice'")print("{} record(s) deleted.".format(row_ct))database.run_in_transaction(delete_singers)

Ruby

# 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=0client.transactiondo|transaction|row_count=transaction.execute_update("DELETE FROM Singers WHERE FirstName = 'Alice'")endputs"#{row_count} record deleted."

The following example, for GoogleSQL-dialect databases only, uses aSTRUCT with bound parametersto update theLastName in rows filtered byFirstName andLastName.

GoogleSQL

C++

voidDmlStructs(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;std::int64_trows_modified=0;autocommit_result=client.Commit([&client,&rows_modified](spanner::Transactionconst&txn)->google::cloud::StatusOr<spanner::Mutations>{autosinger_info=std::make_tuple("Marc","Richards");autosql=spanner::SqlStatement("UPDATE Singers SET FirstName = 'Keith' WHERE ""STRUCT<FirstName String, LastName String>(FirstName, LastName) ""= @name",{{"name", spanner::Value(std::move(singer_info))}});autodml_result=client.ExecuteDml(txn,std::move(sql));if(!dml_result)returnstd::move(dml_result).status();rows_modified=dml_result->RowsModified();returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<rows_modified            <<" update was successful [spanner_dml_structs]\n";}

C#

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;}}

Go

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}

Java

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;});}

Node.js

// 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();}});

PHP

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);    });}

Python

# 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)

Ruby

# 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."

Modify data with the returning DML statements

TheTHEN RETURN clause (GoogleSQL-dialect databases)orRETURNING clause (PostgreSQL-dialect databases)is intended for scenarios where you want to fetch data from modified rows. Thisis especially useful when you want to view unspecified values in the DMLstatements, default values, or generated columns.

To execute returning DML statements using the client library:

Create aread-write transaction.
Call the client library method for query execution and pass in the returningDML statement to obtain results.

The following code example inserts a new row into theSingers table, and itreturns the generated column FullName of the inserted records.

GoogleSQL

C++

voidInsertUsingDmlReturning(google::cloud::spanner::Clientclient){//InsertrecordsintoSINGERStableandreturnthegeneratedcolumn//FullNameoftheinsertedrecordsusing`THEN RETURN FullName`.autocommit=client.Commit([&client](google::cloud::spanner::Transactiontxn)->google::cloud::StatusOr<google::cloud::spanner::Mutations>{autosql=google::cloud::spanner::SqlStatement(R"""(            INSERT INTO Singers (SingerId, FirstName, LastName)              VALUES (12, 'Melissa', 'Garcia'),                     (13, 'Russell', 'Morales'),                     (14, 'Jacqueline', 'Long'),                     (15, 'Dylan', 'Shaw')              THEN RETURN FullName        )""");usingRowType=std::tuple<std::string>;autorows=client.ExecuteQuery(std::move(txn),std::move(sql));//Note:Thismutatormightbere-run,oritseffectsdiscarded,so//changingnon-transactionalstate(e.g.,byproducingoutput)is,//ingeneral,notsomethingtobeimitated.for(auto&row:google::cloud::spanner::StreamOf<RowType>(rows)){if(!row)returnstd::move(row).status();std::cout <<"FullName: " <<std::get<0>(*row) <<"\n";}std::cout <<"Inserted row(s) count: " <<rows.RowsModified() <<"\n";returngoogle::cloud::spanner::Mutations{};});if(!commit)throwstd::move(commit).status();}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassInsertUsingDmlReturningAsyncSample{publicasyncTask<List<string>>InsertUsingDmlReturningAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Insert records into the SINGERS table and return the// generated column FullName of the inserted records using// 'THEN RETURN FullName'.// It is also possible to return all columns of all the// inserted records by using 'THEN RETURN *'.usingvarcmd=connection.CreateDmlCommand(@"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES            (6, 'Melissa', 'Garcia'),            (7, 'Russell', 'Morales'),            (8, 'Jacqueline', 'Long'),            (9, 'Dylan', 'Shaw') THEN RETURN FullName");varreader=awaitcmd.ExecuteReaderAsync();varinsertedSingerNames=newList<string>();while(awaitreader.ReadAsync()){insertedSingerNames.Add(reader.GetFieldValue<string>("FullName"));}Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");returninsertedSingerNames;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcinsertUsingDMLReturning(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Insert records into the SINGERS table and returns the// generated column FullName of the inserted records using// 'THEN RETURN FullName'.// It is also possible to return all columns of all the// inserted records by using 'THEN RETURN *'._,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`INSERT INTO Singers (SingerId, FirstName, LastName)        VALUES (21, 'Melissa', 'Garcia'),               (22, 'Russell', 'Morales'),               (23, 'Jacqueline', 'Long'),               (24, 'Dylan', 'Shaw')        THEN RETURN FullName`,}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}varfullNamestringiferr:=row.Columns(&fullName);err!=nil{returnerr}fmt.Fprintf(w,"%s\n",fullName)}fmt.Fprintf(w,"%d record(s) inserted.\n",iter.RowCount)returnnil})returnerr}

Java

importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.ResultSet;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.Statement;publicclassInsertUsingDmlReturningSample{staticvoidinsertUsingDmlReturning(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";insertUsingDmlReturning(projectId,instanceId,databaseId);}staticvoidinsertUsingDmlReturning(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){finalDatabaseClientdbClient=spanner.getDatabaseClient(DatabaseId.of(projectId,instanceId,databaseId));// Insert records into the SINGERS table and returns the// generated column FullName of the inserted records using// ‘THEN RETURN FullName’.// It is also possible to return all columns of all the// inserted records by using ‘THEN RETURN *’.dbClient.readWritreadWriteTransaction.run(transaction->{Stringsql="INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "+"(12, 'Melissa', 'Garcia'), "+"(13, 'Russell', 'Morales'), "+"(14, 'Jacqueline', 'Long'), "+"(15, 'Dylan', 'Shaw') THEN RETURN FullName";// readWriteTransaction.executeQuery(..) API should be used for executing// DML statements with RETURNING clause.try(ResultSeResultSetet=transaction.executeQuery(StatemenStatement)){while(resultSet.next()){System.out.println(resultSet.getString(0));}System.out.printf("Inserted row(s) count: %d\n",resultSet.getStats().getRowCountExact());}returnnull;});}}}

Node.js

// Imports the Google Cloud client library.const{Spanner}=require('@google-cloud/spanner');/** * 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,});functioninsertUsingDmlReturning(instanceId,databaseId){// Gets a reference to a Cloud Spanner instance and database.constinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rows,stats]=awaittransaction.run({sql:'INSERT Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName) THEN RETURN FullName',params:{id:18,firstName:'Virginia',lastName:'Watson',},});constrowCount=Math.floor(stats[stats.rowCount]);console.log(`Successfully inserted${rowCount} record into the Singers table.`,);rows.forEach(row=>{console.log(row.toJSON().FullName);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});}insertUsingDmlReturning(instanceId,databaseId);

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Inserts sample data into the given database using DML returning. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function insert_dml_returning(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    // Insert records into SINGERS table and returns the generated column    // FullName of the inserted records using ‘THEN RETURN FullName’. It is also    // possible to return all columns of all the inserted records by using    // ‘THEN RETURN *’.    $sql = 'INSERT INTO Singers (SingerId, FirstName, LastName) '        . "VALUES (12, 'Melissa', 'Garcia'), "        . "(13, 'Russell', 'Morales'), "        . "(14, 'Jacqueline', 'Long'), "        . "(15, 'Dylan', 'Shaw') "        . 'THEN RETURN FullName';    $transaction = $database->transaction();    $result = $transaction->execute($sql);    foreach ($result->rows() as $row) {        printf(            '%s inserted.' . PHP_EOL,            $row['FullName'],        );    }    printf(        'Inserted row(s) count: %d' . PHP_EOL,        $result->stats()['rowCountExact']    );    $transaction->commit();}

Python

# 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)# Insert records into the SINGERS table and returns the# generated column FullName of the inserted records using# 'THEN RETURN FullName'.# It is also possible to return all columns of all the# inserted records by using 'THEN RETURN *'.definsert_singers(transaction):results=transaction.execute_sql("INSERT INTO Singers (SingerId, FirstName, LastName) VALUES ""(21, 'Luann', 'Chizoba'), ""(22, 'Denis', 'Patricio'), ""(23, 'Felxi', 'Ronan'), ""(24, 'Dominik', 'Martyna') ""THEN RETURN FullName")forresultinresults:print("FullName:{}".format(*result))print("{} record(s) inserted.".format(results.stats.row_count_exact))database.run_in_transaction(insert_singers)

Ruby

require"google/cloud/spanner"### This is a snippet for showcasing how to use DML return feature with insert# operation.## @param project_id  [String] The ID of the Google Cloud project.# @param instance_id [String] The ID of the spanner instance.# @param database_id [String] The ID of the database.#defspanner_insert_dml_returningproject_id:,instance_id:,database_id:spanner=Google::Cloud::Spanner.newproject:project_idclient=spanner.clientinstance_id,database_idclient.transactiondo|transaction|# Insert records into the SINGERS table and returns the generated column# FullName of the inserted records using ‘THEN RETURN FullName’.# It is also possible to return all columns of all the inserted records# by using ‘THEN RETURN *’.results=transaction.execute_query"INSERT INTO Singers (SingerId, FirstName, LastName)                                         VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')                                         THEN RETURN FullName"results.rows.eachdo|row|puts"Inserted singers with FullName:#{row[:FullName]}"endputs"Inserted row(s) count:#{results.row_count}"endend

PostgreSQL

C++

voidInsertUsingDmlReturning(google::cloud::spanner::Clientclient){//InsertrecordsintoSINGERStableandreturnthegeneratedcolumn//FullNameoftheinsertedrecordsusing`RETURNINGFullName`.autocommit=client.Commit([&client](google::cloud::spanner::Transactiontxn)->google::cloud::StatusOr<google::cloud::spanner::Mutations>{autosql=google::cloud::spanner::SqlStatement(R"""(            INSERT INTO Singers (SingerId, FirstName, LastName)                VALUES (12, 'Melissa', 'Garcia'),                       (13, 'Russell', 'Morales'),                       (14, 'Jacqueline', 'Long'),                       (15, 'Dylan', 'Shaw')                RETURNING FullName        )""");usingRowType=std::tuple<std::string>;autorows=client.ExecuteQuery(std::move(txn),std::move(sql));for(auto&row:google::cloud::spanner::StreamOf<RowType>(rows)){if(!row)returnstd::move(row).status();std::cout <<"FullName: " <<std::get<0>(*row) <<"\n";}std::cout <<"Inserted row(s) count: " <<rows.RowsModified() <<"\n";returngoogle::cloud::spanner::Mutations{};});if(!commit)throwstd::move(commit).status();}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassInsertUsingDmlReturningAsyncPostgresSample{publicasyncTask<List<string>>InsertUsingDmlReturningAsyncPostgres(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Insert records into SINGERS table and return the// generated column FullName of the inserted records// using 'RETURNING FullName'.// It is also possible to return all columns of all the// inserted records by using 'RETURNING *'.usingvarcmd=connection.CreateDmlCommand(@"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES            (6, 'Melissa', 'Garcia'),            (7, 'Russell', 'Morales'),            (8, 'Jacqueline', 'Long'),            (9, 'Dylan', 'Shaw') RETURNING FullName");varreader=awaitcmd.ExecuteReaderAsync();varinsertedSingerNames=newList<string>();while(awaitreader.ReadAsync()){insertedSingerNames.Add(reader.GetFieldValue<string>("fullname"));}Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");returninsertedSingerNames;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcpgInsertUsingDMLReturning(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Insert records into the SINGERS table and returns the// generated column FullName of the inserted records using// 'RETURNING FullName'.// It is also possible to return all columns of all the// inserted records by using 'RETURNING *'._,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`INSERT INTO Singers (SingerId, FirstName, LastName)        VALUES (21, 'Melissa', 'Garcia'),               (22, 'Russell', 'Morales'),               (23, 'Jacqueline', 'Long'),               (24, 'Dylan', 'Shaw')        RETURNING FullName`,}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}varfullNamestringiferr:=row.Columns(&fullName);err!=nil{returnerr}fmt.Fprintf(w,"%s\n",fullName)}fmt.Fprintf(w,"%d record(s) inserted.\n",iter.RowCount)returnnil})returnerr}

Java

importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.ResultSet;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.Statement;publicclassPgInsertUsingDmlReturningSample{staticvoidinsertUsingDmlReturning(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";insertUsingDmlReturning(projectId,instanceId,databaseId);}staticvoidinsertUsingDmlReturning(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){finalDatabaseClientdbClient=spanner.getDatabaseClient(DatabaseId.of(projectId,instanceId,databaseId));// Insert records into SINGERS table and returns the// generated column FullName of the inserted records// using ‘RETURNING FullName’.// It is also possible to return all columns of all the// inserted records by using ‘RETURNING *’.dbClient.readWritreadWriteTransaction.run(transaction->{Stringsql="INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "+"(12, 'Melissa', 'Garcia'), "+"(13, 'Russell', 'Morales'), "+"(14, 'Jacqueline', 'Long'), "+"(15, 'Dylan', 'Shaw') RETURNING FullName";// readWriteTransaction.executeQuery(..) API should be used for executing// DML statements with RETURNING clause.try(ResultSeResultSetet=transaction.executeQuery(StatemenStatement)){while(resultSet.next()){System.out.println(resultSet.getString(0));}System.out.printf("Inserted row(s) count: %d\n",resultSet.getStats().getRowCountExact());}returnnull;});}}}

Node.js

// Imports the Google Cloud client library.const{Spanner}=require('@google-cloud/spanner');/** * 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,});functionpgInsertUsingDmlReturning(instanceId,databaseId){// Gets a reference to a Cloud Spanner instance and database.constinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rows,stats]=awaittransaction.run({sql:'INSERT Into Singers (SingerId, FirstName, LastName) VALUES ($1, $2, $3) RETURNING FullName',params:{p1:18,p2:'Virginia',p3:'Watson',},});constrowCount=Math.floor(stats[stats.rowCount]);console.log(`Successfully inserted${rowCount} record into the Singers table.`,);rows.forEach(row=>{console.log(row.toJSON().fullname);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});}pgInsertUsingDmlReturning(instanceId,databaseId);

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Inserts sample data into the given postgresql database using DML returning. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function pg_insert_dml_returning(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    // Insert records into SINGERS table and returns the generated column    // FullName of the inserted records using ‘RETURNING FullName’. It is also    // possible to return all columns of all the inserted records by using    // ‘RETURNING *’.    $sql = 'INSERT INTO Singers (Singerid, FirstName, LastName) '      . "VALUES (12, 'Melissa', 'Garcia'), "      . "(13, 'Russell', 'Morales'), "      . "(14, 'Jacqueline', 'Long'), "      . "(15, 'Dylan', 'Shaw') "      . 'RETURNING FullName';    $transaction = $database->transaction();    $result = $transaction->execute($sql);    foreach ($result->rows() as $row) {        printf(            '%s inserted.' . PHP_EOL,            $row['fullname'],        );    }    printf(        'Inserted row(s) count: %d' . PHP_EOL,        $result->stats()['rowCountExact']    );    $transaction->commit();}

Python

# 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)# Insert records into the SINGERS table and returns the# generated column FullName of the inserted records using# 'RETURNING FullName'.# It is also possible to return all columns of all the# inserted records by using 'RETURNING *'.definsert_singers(transaction):results=transaction.execute_sql("INSERT INTO Singers (SingerId, FirstName, LastName) VALUES ""(21, 'Luann', 'Chizoba'), ""(22, 'Denis', 'Patricio'), ""(23, 'Felxi', 'Ronan'), ""(24, 'Dominik', 'Martyna') ""RETURNING FullName")forresultinresults:print("FullName:{}".format(*result))print("{} record(s) inserted.".format(results.stats.row_count_exact))database.run_in_transaction(insert_singers)

Ruby

require"google/cloud/spanner"### This is a snippet for showcasing how to use DML return feature with insert# operation in PostgreSql.## @param project_id  [String] The ID of the Google Cloud project.# @param instance_id [String] The ID of the spanner instance.# @param database_id [String] The ID of the database.#defspanner_postgresql_insert_dml_returningproject_id:,instance_id:,database_id:spanner=Google::Cloud::Spanner.newproject:project_idclient=spanner.clientinstance_id,database_idclient.transactiondo|transaction|# Insert records into SINGERS table and returns the generated column# FullName of the inserted records using ‘RETURNING FullName’.# It is also possible to return all columns of all the inserted# records by using ‘RETURNING *’.results=transaction.execute_query"INSERT INTO Singers (SingerId, FirstName, LastName)                                         VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')                                         RETURNING FullName"results.rows.eachdo|row|puts"Inserted singers with FullName:#{row[:fullname]}"endputs"Inserted row(s) count:#{results.row_count}"endend

The following code example updates theMarketingBudget column of theAlbumstable based on aWHERE clause, and it returns the modifiedMarketingBudgetcolumn of the updated records.

GoogleSQL

C++

voidUpdateUsingDmlReturning(google::cloud::spanner::Clientclient){//UpdateMarketingBudgetcolumnforrecordssatisfyingaparticular//conditionandreturnthemodifiedMarketingBudgetcolumnofthe//updatedrecordsusing`THEN RETURN MarketingBudget`.autocommit=client.Commit([&client](google::cloud::spanner::Transactiontxn)->google::cloud::StatusOr<google::cloud::spanner::Mutations>{autosql=google::cloud::spanner::SqlStatement(R"""(            UPDATE Albums SET MarketingBudget = MarketingBudget * 2              WHERE SingerId = 1 AND AlbumId = 1              THEN RETURN MarketingBudget        )""");usingRowType=std::tuple<absl::optional<std::int64_t>>;autorows=client.ExecuteQuery(std::move(txn),std::move(sql));//Note:Thismutatormightbere-run,oritseffectsdiscarded,so//changingnon-transactionalstate(e.g.,byproducingoutput)is,//ingeneral,notsomethingtobeimitated.for(auto&row:google::cloud::spanner::StreamOf<RowType>(rows)){if(!row)returnstd::move(row).status();std::cout <<"MarketingBudget: ";if(std::get<0>(*row).has_value()){std::cout <<*std::get<0>(*row);}else{std::cout <<"NULL";}std::cout <<"\n";}std::cout <<"Updated row(s) count: " <<rows.RowsModified() <<"\n";returngoogle::cloud::spanner::Mutations{};});if(!commit)throwstd::move(commit).status();}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassUpdateUsingDmlReturningAsyncSample{publicasyncTask<List<long>>UpdateUsingDmlReturningAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Update MarketingBudget column for records satisfying// a particular condition and return the modified// MarketingBudget column of the updated records using// 'THEN RETURN MarketingBudget'.// It is also possible to return all columns of all the// updated records by using 'THEN RETURN *'.usingvarcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget");varreader=awaitcmd.ExecuteReaderAsync();varupdatedMarketingBudgets=newList<long>();while(awaitreader.ReadAsync()){updatedMarketingBudgets.Add(reader.GetFieldValue<long>("MarketingBudget"));}Console.WriteLine($"{updatedMarketingBudgets.Count} row(s) updated...");returnupdatedMarketingBudgets;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcupdateUsingDMLReturning(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Update MarketingBudget column for records satisfying// a particular condition and returns the modified// MarketingBudget column of the updated records using// 'THEN RETURN MarketingBudget'.// It is also possible to return all columns of all the// updated records by using 'THEN RETURN *'._,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`UPDATE AlbumsSET MarketingBudget = MarketingBudget * 2WHERE SingerId = 1 and AlbumId = 1THEN RETURN MarketingBudget`,}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}varmarketingBudgetint64iferr:=row.Columns(&marketingBudget);err!=nil{returnerr}fmt.Fprintf(w,"%d\n",marketingBudget)}fmt.Fprintf(w,"%d record(s) updated.\n",iter.RowCount)returnnil})returnerr}

Java

importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.ResultSet;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.Statement;publicclassUpdateUsingDmlReturningSample{staticvoidupdateUsingDmlReturning(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";updateUsingDmlReturning(projectId,instanceId,databaseId);}staticvoidupdateUsingDmlReturning(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){finalDatabaseClientdbClient=spanner.getDatabaseClient(DatabaseId.of(projectId,instanceId,databaseId));// Update MarketingBudget column for records satisfying// a particular condition and returns the modified// MarketingBudget column of the updated records using// ‘THEN RETURN MarketingBudget’.// It is also possible to return all columns of all the// updated records by using ‘THEN RETURN *’.dbClient.readWritreadWriteTransaction.run(transaction->{Stringsql="UPDATE Albums "+"SET MarketingBudget = MarketingBudget * 2 "+"WHERE SingerId = 1 and AlbumId = 1 "+"THEN RETURN MarketingBudget";// readWriteTransaction.executeQuery(..) API should be used for executing// DML statements with RETURNING clause.try(ResultSeResultSetet=transaction.executeQuery(StatemenStatement)){while(resultSet.next()){System.out.printf("%d\n",resultSet.getLong(0));}System.out.printf("Updated row(s) count: %d\n",resultSet.getStats().getRowCountExact());}returnnull;});}}}

Node.js

// Imports the Google Cloud client library.const{Spanner}=require('@google-cloud/spanner');/** * 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,});functionupdateUsingDmlReturning(instanceId,databaseId){// Gets a reference to a Cloud Spanner instance and database.constinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rows,stats]=awaittransaction.run({sql:'UPDATE Albums SET MarketingBudget = 2000000 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget',});constrowCount=Math.floor(stats[stats.rowCount]);console.log(`Successfully updated${rowCount} record into the Albums table.`,);rows.forEach(row=>{console.log(row.toJSON().MarketingBudget);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});}updateUsingDmlReturning(instanceId,databaseId);

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Update the given database using DML returning. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function update_dml_returning(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $transaction = $database->transaction();    // Update MarketingBudget column for records satisfying a particular    // condition and returns the modified MarketingBudget column of the updated    // records using ‘THEN RETURN MarketingBudget’. It is also possible to return    // all columns of all the updated records by using ‘THEN RETURN *’.    $result = $transaction->execute(        'UPDATE Albums '        . 'SET MarketingBudget = MarketingBudget * 2 '        . 'WHERE SingerId = 1 and AlbumId = 1 '        . 'THEN RETURN MarketingBudget'    );    foreach ($result->rows() as $row) {        printf('MarketingBudget: %s' . PHP_EOL, $row['MarketingBudget']);    }    printf(        'Updated row(s) count: %d' . PHP_EOL,        $result->stats()['rowCountExact']    );    $transaction->commit();}

Python

# 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)# Update MarketingBudget column for records satisfying# a particular condition and returns the modified# MarketingBudget column of the updated records using# 'THEN RETURN MarketingBudget'.# It is also possible to return all columns of all the# updated records by using 'THEN RETURN *'.defupdate_albums(transaction):results=transaction.execute_sql("UPDATE Albums ""SET MarketingBudget = MarketingBudget * 2 ""WHERE SingerId = 1 and AlbumId = 1 ""THEN RETURN MarketingBudget")forresultinresults:print("MarketingBudget:{}".format(*result))print("{} record(s) updated.".format(results.stats.row_count_exact))database.run_in_transaction(update_albums)

Ruby

require"google/cloud/spanner"### This is a snippet for showcasing how to use DML return feature with update# operation.## @param project_id  [String] The ID of the Google Cloud project.# @param instance_id [String] The ID of the spanner instance.# @param database_id [String] The ID of the database.#defspanner_update_dml_returningproject_id:,instance_id:,database_id:spanner=Google::Cloud::Spanner.newproject:project_idclient=spanner.clientinstance_id,database_idclient.transactiondo|transaction|# Update MarketingBudget column for records satisfying a particular# condition and returns the modified MarketingBudget column of the# updated records using ‘THEN RETURN MarketingBudget’.## It is also possible to return all columns of all the updated records# by using ‘THEN RETURN *’.results=transaction.execute_query"UPDATE Albums SET MarketingBudget = MarketingBudget * 2                                         WHERE SingerId = 1 and AlbumId = 1                                         THEN RETURN MarketingBudget"results.rows.eachdo|row|puts"Updated Album with MarketingBudget:#{row[:MarketingBudget]}"endputs"Updated row(s) count:#{results.row_count}"endend

PostgreSQL

C++

voidUpdateUsingDmlReturning(google::cloud::spanner::Clientclient){//UpdateMarketingBudgetcolumnforrecordssatisfyingaparticular//conditionandreturnthemodifiedMarketingBudgetcolumnofthe//updatedrecordsusing`RETURNINGMarketingBudget`.autocommit=client.Commit([&client](google::cloud::spanner::Transactiontxn)->google::cloud::StatusOr<google::cloud::spanner::Mutations>{autosql=google::cloud::spanner::SqlStatement(R"""(            UPDATE Albums SET MarketingBudget = MarketingBudget * 2              WHERE SingerId = 1 AND AlbumId = 1              RETURNING MarketingBudget        )""");usingRowType=std::tuple<absl::optional<std::int64_t>>;autorows=client.ExecuteQuery(std::move(txn),std::move(sql));for(auto&row:google::cloud::spanner::StreamOf<RowType>(rows)){if(!row)returnstd::move(row).status();std::cout <<"MarketingBudget: ";if(std::get<0>(*row).has_value()){std::cout <<*std::get<0>(*row);}else{std::cout <<"NULL";}std::cout <<"\n";}std::cout <<"Updated row(s) count: " <<rows.RowsModified() <<"\n";returngoogle::cloud::spanner::Mutations{};});if(!commit)throwstd::move(commit).status();}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassUpdateUsingDmlReturningAsyncPostgresSample{publicasyncTask<List<long>>UpdateUsingDmlReturningAsyncPostgres(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Update MarketingBudget column for records satisfying// a particular condition and return the modified// MarketingBudget column of the updated records using// 'RETURNING MarketingBudget'.// It is also possible to return all columns of all the// updated records by using 'RETURNING *'.usingvarcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 14 and AlbumId = 20 RETURNING MarketingBudget");varreader=awaitcmd.ExecuteReaderAsync();varupdatedMarketingBudgets=newList<long>();while(awaitreader.ReadAsync()){updatedMarketingBudgets.Add(reader.GetFieldValue<long>("marketingbudget"));}Console.WriteLine($"{updatedMarketingBudgets.Count} row(s) updated...");returnupdatedMarketingBudgets;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcpgUpdateUsingDMLReturning(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Update MarketingBudget column for records satisfying// a particular condition and returns the modified// MarketingBudget column of the updated records using// 'RETURNING MarketingBudget'.// It is also possible to return all columns of all the// updated records by using 'RETURNING *'._,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`UPDATE AlbumsSET MarketingBudget = MarketingBudget * 2WHERE SingerId = 1 and AlbumId = 1RETURNING MarketingBudget`,}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}varmarketingBudgetint64iferr:=row.Columns(&marketingBudget);err!=nil{returnerr}fmt.Fprintf(w,"%d\n",marketingBudget)}fmt.Fprintf(w,"%d record(s) updated.\n",iter.RowCount)returnnil})returnerr}

Java

importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.ResultSet;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.Statement;publicclassPgUpdateUsingDmlReturningSample{staticvoidupdateUsingDmlReturning(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";updateUsingDmlReturning(projectId,instanceId,databaseId);}staticvoidupdateUsingDmlReturning(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){finalDatabaseClientdbClient=spanner.getDatabaseClient(DatabaseId.of(projectId,instanceId,databaseId));// Update MarketingBudget column for records satisfying// a particular condition and returns the modified// MarketingBudget column of the updated records using// ‘RETURNING MarketingBudget’.// It is also possible to return all columns of all the// updated records by using ‘RETURNING *’.dbClient.readWritreadWriteTransaction.run(transaction->{Stringsql="UPDATE Albums "+"SET MarketingBudget = MarketingBudget * 2 "+"WHERE SingerId = 1 and AlbumId = 1 "+"RETURNING MarketingBudget";// readWriteTransaction.executeQuery(..) API should be used for executing// DML statements with RETURNING clause.try(ResultSeResultSetet=transaction.executeQuery(StatemenStatement)){while(resultSet.next()){System.out.printf("%d\n",resultSet.getLong(0));}System.out.printf("Updated row(s) count: %d\n",resultSet.getStats().getRowCountExact());}returnnull;});}}}

Node.js

// Imports the Google Cloud client library.const{Spanner}=require('@google-cloud/spanner');/** * 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,});functionpgUpdateUsingDmlReturning(instanceId,databaseId){// Gets a reference to a Cloud Spanner instance and database.constinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rows,stats]=awaittransaction.run({sql:'UPDATE singers SET FirstName = $1, LastName = $2 WHERE singerid = $3 RETURNING FullName',params:{p1:'Virginia1',p2:'Watson1',p3:18,},});constrowCount=Math.floor(stats[stats.rowCount]);console.log(`Successfully updated${rowCount} record into the Singers table.`,);rows.forEach(row=>{console.log(row.toJSON().fullname);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});}pgUpdateUsingDmlReturning(instanceId,databaseId);

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Update the given postgresql database using DML returning. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function pg_update_dml_returning(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $transaction = $database->transaction();    // Update MarketingBudget column for records satisfying a particular    // condition and returns the modified MarketingBudget column of the updated    // records using ‘RETURNING MarketingBudget’. It is also possible to return    // all columns of all the updated records by using ‘RETURNING *’.    $result = $transaction->execute(        'UPDATE Albums '        . 'SET MarketingBudget = MarketingBudget * 2 '        . 'WHERE SingerId = 1 and AlbumId = 1 '        . 'RETURNING MarketingBudget'    );    foreach ($result->rows() as $row) {        printf('MarketingBudget: %s' . PHP_EOL, $row['marketingbudget']);    }    printf(        'Updated row(s) count: %d' . PHP_EOL,        $result->stats()['rowCountExact']    );    $transaction->commit();}

Python

# 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)# Update MarketingBudget column for records satisfying# a particular condition and returns the modified# MarketingBudget column of the updated records using# 'RETURNING MarketingBudget'.# It is also possible to return all columns of all the# updated records by using 'RETURNING *'.defupdate_albums(transaction):results=transaction.execute_sql("UPDATE Albums ""SET MarketingBudget = MarketingBudget * 2 ""WHERE SingerId = 1 and AlbumId = 1 ""RETURNING MarketingBudget")forresultinresults:print("MarketingBudget:{}".format(*result))print("{} record(s) updated.".format(results.stats.row_count_exact))database.run_in_transaction(update_albums)

Ruby

require"google/cloud/spanner"### This is a snippet for showcasing how to use DML return feature with update# operation in PostgreSql.## @param project_id  [String] The ID of the Google Cloud project.# @param instance_id [String] The ID of the spanner instance.# @param database_id [String] The ID of the database.#defspanner_postgresql_update_dml_returningproject_id:,instance_id:,database_id:spanner=Google::Cloud::Spanner.newproject:project_idclient=spanner.clientinstance_id,database_idclient.transactiondo|transaction|# Update MarketingBudget column for records satisfying a particular# condition and returns the modified MarketingBudget column of the# updated records using ‘RETURNING MarketingBudget’.# It is also possible to return all columns of all the updated records# by using ‘RETURNING *’.results=transaction.execute_query"UPDATE Albums SET MarketingBudget = MarketingBudget * 2                                         WHERE SingerId = 1 and AlbumId = 1                                         RETURNING MarketingBudget"results.rows.eachdo|row|puts"Updated Albums with MarketingBudget:#{row[:marketingbudget]}"endputs"Updated row(s) count:#{results.row_count}"endend

The following code example deletes all the rows in theSingers table where theFirstName column isAlice, and it returns theSingerId andFullNamecolumn of the deleted records.

GoogleSQL

C++

voidDeleteUsingDmlReturning(google::cloud::spanner::Clientclient){//DeleterecordsfromSINGERStablesatisfyingaparticularcondition//andreturntheSingerIdandFullNamecolumnofthedeletedrecords//using`THEN RETURN SingerId, FullName'.  auto commit = client.Commit(      [&client](google::cloud::spanner::Transaction txn)          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {        auto sql = google::cloud::spanner::SqlStatement(R"""(            DELETE FROM Singers              WHERE FirstName = 'Alice'              THEN RETURN SingerId, FullName        )""");        using RowType = std::tuple<std::int64_t, std::string>;        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));        // Note: This mutator might be re-run, or its effects discarded, so        // changing non-transactional state (e.g., by producing output) is,        // in general, not something to be imitated.        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {          if (!row) return std::move(row).status();          std::cout << "SingerId: " << std::get<0>(*row) << " ";          std::cout << "FullName: " << std::get<1>(*row) << "\n";        }        std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";        return google::cloud::spanner::Mutations{};      });  if (!commit) throw std::move(commit).status();}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading;usingSystem.Threading.Tasks;publicclassDeleteUsingDmlReturningAsyncSample{publicasyncTask<List<string>>DeleteUsingDmlReturningAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Delete records from SINGERS table satisfying a// particular condition and return the SingerId// and FullName column of the deleted records using// 'THEN RETURN SingerId, FullName'.// It is also possible to return all columns of all the// deleted records by using 'THEN RETURN *'.usingvarcmd=connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName");varreader=awaitcmd.ExecuteReaderAsync();vardeletedSingerNames=newList<string>();while(awaitreader.ReadAsync()){deletedSingerNames.Add(reader.GetFieldValue<string>("FullName"));}Console.WriteLine($"{deletedSingerNames.Count} row(s) deleted...");returndeletedSingerNames;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcdeleteUsingDMLReturning(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Delete records from SINGERS table satisfying a// particular condition and returns the SingerId// and FullName column of the deleted records using// 'THEN RETURN SingerId, FullName'.// It is also possible to return all columns of all the// deleted records by using 'THEN RETURN *'._,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`DELETE FROM Singers WHERE FirstName = 'Alice'        THEN RETURN SingerId, FullName`,}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}var(singerIDint64fullNamestring)iferr:=row.Columns(&singerID,&fullName);err!=nil{returnerr}fmt.Fprintf(w,"%d %s\n",singerID,fullName)}fmt.Fprintf(w,"%d record(s) deleted.\n",iter.RowCount)returnnil})returnerr}

Java

importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.ResultSet;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.Statement;publicclassDeleteUsingDmlReturningSample{staticvoiddeleteUsingDmlReturningSample(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";deleteUsingDmlReturningSample(projectId,instanceId,databaseId);}staticvoiddeleteUsingDmlReturningSample(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){finalDatabaseClientdbClient=spanner.getDatabaseClient(DatabaseId.of(projectId,instanceId,databaseId));// Delete records from SINGERS table satisfying a// particular condition and returns the SingerId// and FullName column of the deleted records using// ‘THEN RETURN SingerId, FullName’.// It is also possible to return all columns of all the// deleted records by using ‘THEN RETURN *’.dbClient.readWritreadWriteTransaction.run(transaction->{Stringsql="DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName";// readWriteTransaction.executeQuery(..) API should be used for executing// DML statements with RETURNING clause.try(ResultSeResultSetet=transaction.executeQuery(StatemenStatement)){while(resultSet.next()){System.out.printf("%d %s\n",resultSet.getLong(0),resultSet.getString(1));}System.out.printf("Deleted row(s) count: %d\n",resultSet.getStats().getRowCountExact());}returnnull;});}}}

Node.js

// Imports the Google Cloud client library.const{Spanner}=require('@google-cloud/spanner');/** * 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,});functiondeleteUsingDmlReturning(instanceId,databaseId){// Gets a reference to a Cloud Spanner instance and database.constinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rows,stats]=awaittransaction.run({sql:'DELETE FROM Singers WHERE SingerId = 18 THEN RETURN FullName',});constrowCount=Math.floor(stats[stats.rowCount]);console.log(`Successfully deleted${rowCount} record from the Singers table.`,);rows.forEach(row=>{console.log(row.toJSON().FullName);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});}deleteUsingDmlReturning(instanceId,databaseId);

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Delete data from the given database using DML returning. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function delete_dml_returning(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $transaction = $database->transaction();    // Delete records from SINGERS table satisfying a particular condition and    // returns the SingerId and FullName column of the deleted records using    // 'THEN RETURN SingerId, FullName'. It is also possible to return all columns    //  of all the deleted records by using 'THEN RETURN *'.    $result = $transaction->execute(        "DELETE FROM Singers WHERE FirstName = 'Alice' "        . 'THEN RETURN SingerId, FullName',    );    foreach ($result->rows() as $row) {        printf(            '%d %s.' . PHP_EOL,            $row['SingerId'],            $row['FullName']        );    }    printf(        'Deleted row(s) count: %d' . PHP_EOL,        $result->stats()['rowCountExact']    );    $transaction->commit();}

Python

# 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)# Delete records from SINGERS table satisfying a# particular condition and returns the SingerId# and FullName column of the deleted records using# 'THEN RETURN SingerId, FullName'.# It is also possible to return all columns of all the# deleted records by using 'THEN RETURN *'.defdelete_singers(transaction):results=transaction.execute_sql("DELETE FROM Singers WHERE FirstName = 'David' ""THEN RETURN SingerId, FullName")forresultinresults:print("SingerId:{}, FullName:{}".format(*result))print("{} record(s) deleted.".format(results.stats.row_count_exact))database.run_in_transaction(delete_singers)

Ruby

require"google/cloud/spanner"### This is a snippet for showcasing how to use DML return feature with delete# operation.## @param project_id  [String] The ID of the Google Cloud project.# @param instance_id [String] The ID of the spanner instance.# @param database_id [String] The ID of the database.#defspanner_delete_dml_returningproject_id:,instance_id:,database_id:spanner=Google::Cloud::Spanner.newproject:project_idclient=spanner.clientinstance_id,database_idclient.transactiondo|transaction|# Delete records from SINGERS table satisfying a particular condition and# returns the SingerId and FullName column of the deleted records using# ‘THEN RETURN SingerId, FullName’.# It is also possible to return all columns of all the deleted records# by using ‘THEN RETURN *’.results=transaction.execute_query"DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName"results.rows.eachdo|row|puts"Deleted singer with SingerId:#{row[:SingerId]}, FullName:#{row[:FullName]}"endputs"Deleted row(s) count:#{results.row_count}"endend

PostgreSQL

C++

voidDeleteUsingDmlReturning(google::cloud::spanner::Clientclient){//DeleterecordsfromSINGERStablesatisfyingaparticularcondition//andreturntheSingerIdandFullNamecolumnofthedeletedrecords//using`RETURNINGSingerId,FullName'.  auto commit = client.Commit(      [&client](google::cloud::spanner::Transaction txn)          -> google::cloud::StatusOr<google::cloud::spanner::Mutations> {        auto sql = google::cloud::spanner::SqlStatement(R"""(            DELETE FROM Singers              WHERE FirstName = 'Alice'              RETURNING SingerId, FullName        )""");        using RowType = std::tuple<std::int64_t, std::string>;        auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));        for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {          if (!row) return std::move(row).status();          std::cout << "SingerId: " << std::get<0>(*row) << " ";          std::cout << "FullName: " << std::get<1>(*row) << "\n";        }        std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";        return google::cloud::spanner::Mutations{};      });  if (!commit) throw std::move(commit).status();}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Threading.Tasks;publicclassDeleteUsingDmlReturningAsyncPostgresSample{publicasyncTask<List<string>>DeleteUsingDmlReturningAsyncPostgres(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();// Delete records from SINGERS table satisfying a// particular condition and return the SingerId// and FullName column of the deleted records using// 'RETURNING SingerId, FullName'.// It is also possible to return all columns of all the// deleted records by using 'RETURNING *'.usingvarcmd=connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Lata' RETURNING SingerId, FullName");varreader=awaitcmd.ExecuteReaderAsync();vardeletedSingerNames=newList<string>();while(awaitreader.ReadAsync()){deletedSingerNames.Add(reader.GetFieldValue<string>("fullname"));}Console.WriteLine($"{deletedSingerNames.Count} row(s) deleted...");returndeletedSingerNames;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcpgDeleteUsingDMLReturning(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()// Delete records from SINGERS table satisfying a// particular condition and returns the SingerId// and FullName column of the deleted records using// 'RETURNING SingerId, FullName'.// It is also possible to return all columns of all the// deleted records by using 'RETURNING *'._,err=client.ReadWriteTransaction(ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{stmt:=spanner.Statement{SQL:`DELETE FROM Singers WHERE FirstName = 'Alice'        RETURNING SingerId, FullName`,}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}var(singerIDint64fullNamestring)iferr:=row.Columns(&singerID,&fullName);err!=nil{returnerr}fmt.Fprintf(w,"%d %s\n",singerID,fullName)}fmt.Fprintf(w,"%d record(s) deleted.\n",iter.RowCount)returnnil})returnerr}

Java

importcom.google.cloud.spanner.DatabaseClient;importcom.google.cloud.spanner.DatabaseId;importcom.google.cloud.spanner.ResultSet;importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.Statement;publicclassPgDeleteUsingDmlReturningSample{staticvoiddeleteUsingDmlReturningSample(){// TODO(developer): Replace these variables before running the sample.finalStringprojectId="my-project";finalStringinstanceId="my-instance";finalStringdatabaseId="my-database";deleteUsingDmlReturningSample(projectId,instanceId,databaseId);}staticvoiddeleteUsingDmlReturningSample(StringprojectId,StringinstanceId,StringdatabaseId){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService()){finalDatabaseClientdbClient=spanner.getDatabaseClient(DatabaseId.of(projectId,instanceId,databaseId));// Delete records from SINGERS table satisfying a// particular condition and returns the SingerId// and FullName column of the deleted records using// ‘RETURNING SingerId, FullName’.// It is also possible to return all columns of all the// deleted records by using ‘RETURNING *’.dbClient.readWritreadWriteTransaction.run(transaction->{Stringsql="DELETE FROM Singers WHERE FirstName = 'Alice' RETURNING SingerId, FullName";// readWriteTransaction.executeQuery(..) API should be used for executing// DML statements with RETURNING clause.try(ResultSeResultSetet=transaction.executeQuery(StatemenStatement)){while(resultSet.next()){System.out.printf("%d %s\n",resultSet.getLong(0),resultSet.getString(1));}System.out.printf("Deleted row(s) count: %d\n",resultSet.getStats().getRowCountExact());}returnnull;});}}}

Node.js

// Imports the Google Cloud client library.const{Spanner}=require('@google-cloud/spanner');/** * 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,});functionpgDeleteUsingDmlReturning(instanceId,databaseId){// Gets a reference to a Cloud Spanner instance and database.constinstance=spanner.instance(instanceId);constdatabase=instance.database(databaseId);database.runTransaction(async(err,transaction)=>{if(err){console.error(err);return;}try{const[rows,stats]=awaittransaction.run({sql:'DELETE FROM Singers WHERE SingerId = 18 RETURNING FullName',});constrowCount=Math.floor(stats[stats.rowCount]);console.log(`Successfully deleted${rowCount} record from the Singers table.`,);rows.forEach(row=>{console.log(row.toJSON().fullname);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});}pgDeleteUsingDmlReturning(instanceId,databaseId);

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Delete data from the given postgresql database using DML returning. * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function pg_delete_dml_returning(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $transaction = $database->transaction();    // Delete records from SINGERS table satisfying a particular condition and    // returns the SingerId and FullName column of the deleted records using    // ‘RETURNING SingerId, FullName’. It is also possible to return all columns    //  of all the deleted records by using ‘RETURNING *’.    $result = $transaction->execute(        "DELETE FROM Singers WHERE FirstName = 'Alice' "        . 'RETURNING SingerId, FullName',    );    foreach ($result->rows() as $row) {        printf(            '%d %s.' . PHP_EOL,            $row['singerid'],            $row['fullname']        );    }    printf(        'Deleted row(s) count: %d' . PHP_EOL,        $result->stats()['rowCountExact']    );    $transaction->commit();}

Python

# 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)# Delete records from SINGERS table satisfying a# particular condition and returns the SingerId# and FullName column of the deleted records using# 'RETURNING SingerId, FullName'.# It is also possible to return all columns of all the# deleted records by using 'RETURNING *'.defdelete_singers(transaction):results=transaction.execute_sql("DELETE FROM Singers WHERE FirstName = 'David' ""RETURNING SingerId, FullName")forresultinresults:print("SingerId:{}, FullName:{}".format(*result))print("{} record(s) deleted.".format(results.stats.row_count_exact))database.run_in_transaction(delete_singers)

Ruby

require"google/cloud/spanner"### This is a snippet for showcasing how to use DML return feature with delete# operation in PostgreSql.## @param project_id  [String] The ID of the Google Cloud project.# @param instance_id [String] The ID of the spanner instance.# @param database_id [String] The ID of the database.#defspanner_postgresql_delete_dml_returningproject_id:,instance_id:,database_id:spanner=Google::Cloud::Spanner.newproject:project_idclient=spanner.clientinstance_id,database_idclient.transactiondo|transaction|# Delete records from SINGERS table satisfying a particular condition and# returns the SingerId and FullName column of the deleted records using# ‘RETURNING SingerId, FullName’.# It is also possible to return all columns of all the deleted records# by using ‘RETURNING *’.results=transaction.execute_query"DELETE FROM singers WHERE firstname = 'Alice' RETURNING SingerId, FullName"results.rows.eachdo|row|puts"Deleted singer with SingerId:#{row[:singerid]}, FullName:#{row[:fullname]}"endputs"Deleted row(s) count:#{results.row_count}"endend

Read data written in the same transaction

Changes you make using DML statements are visible to subsequent statements inthe same transaction. This is different from usingmutations, where changes arenot visible until the transaction commits.

Spanner checks the constraints after every DML statement. This isdifferent from using mutations, where Spanner buffers mutationsin the client until commit and checks constraints at commit time. Evaluating theconstraints after each statement allows Spanner to guarantee thatthe data that a DML statement returns is consistent with the schema.

The following example updates a row in theSingers table, then executes aSELECT statement to print the new values.

C++

voidDmlWriteThenRead(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;using::google::cloud::StatusOr;autocommit_result=client.Commit([&client](spanner::Transactiontxn)->StatusOr<spanner::Mutations>{autoinsert=client.ExecuteDml(txn,spanner::SqlStatement("INSERT INTO Singers (SingerId, FirstName, LastName)""  VALUES (11, 'Timothy', 'Campbell')"));if(!insert)returnstd::move(insert).status();// Read newly inserted record.spanner::SqlStatementselect("SELECT FirstName, LastName FROM Singers where SingerId = 11");usingRowType=std::tuple<std::string,std::string>;autorows=client.ExecuteQuery(std::move(txn),std::move(select));// Note: This mutator might be re-run, or its effects discarded, so// changing non-transactional state (e.g., by producing output) is,// in general, not something to be imitated.for(autoconst&row:spanner::StreamOf<RowType>(rows)){if(!row)returnstd::move(row).status();std::cout <<"FirstName: " <<std::get<0>(*row) <<"\t";std::cout <<"LastName: " <<std::get<1>(*row) <<"\n";}returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Write then read succeeded [spanner_dml_write_then_read]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassWriteAndReadUsingDmlCoreAsyncSample{publicasyncTask<int>WriteAndReadUsingDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcreateDmlCmd=connection.CreateDmlCommand(@"INSERT Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')");introwCount=awaitcreateDmlCmd.ExecuteNonQueryAsync();Console.WriteLine($"{rowCount} row(s) inserted...");// Read newly inserted record.usingvarcreateSelectCmd=connection.CreateSelectCommand(@"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11");usingvarreader=awaitcreateSelectCmd.ExecuteReaderAsync();while(awaitreader.ReadAsync()){Console.WriteLine($"{reader.GetFieldValue<string>("FirstName")}  {reader.GetFieldValue<string>("LastName")}");}returnrowCount;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner""google.golang.org/api/iterator")funcwriteAndReadUsingDML(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{// Insert Recordstmt:=spanner.Statement{SQL:`INSERT Singers (SingerId, FirstName, LastName)VALUES (11, 'Timothy', 'Campbell')`,}rowCount,err:=txn.Update(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) inserted.\n",rowCount)// Read newly inserted recordstmt=spanner.Statement{SQL:`SELECT FirstName, LastName FROM Singers WHERE SingerId = 11`}iter:=txn.Query(ctx,stmt)deferiter.Stop()for{row,err:=iter.Next()iferr==iterator.Done||err!=nil{break}varfirstName,lastNamestringiferr:=row.ColumnByName("FirstName",&firstName);err!=nil{returnerr}iferr:=row.ColumnByName("LastName",&lastName);err!=nil{returnerr}fmt.Fprintf(w,"Found record name with %s, %s",firstName,lastName)}returnerr})returnerr}

Java

staticvoidwriteAndReadUsingDml(DatabaseClientdbClient){dbClient.readWriteTransaction().run(transaction->{// Insert record.Stringsql="INSERT INTO Singers (SingerId, FirstName, LastName) "+" VALUES (11, 'Timothy', 'Campbell')";longrowCount=transaction.executeUpdate(Statement.of(sql));System.out.printf("%d record inserted.\n",rowCount);// Read newly inserted record.sql="SELECT FirstName, LastName FROM Singers WHERE SingerId = 11";// We use a try-with-resource block to automatically release resources held by// ResultSet.try(ResultSetresultSet=transaction.executeQuery(Statement.of(sql))){while(resultSet.next()){System.out.printf("%s %s\n",resultSet.getString("FirstName"),resultSet.getString("LastName"));}}returnnull;});}

Node.js

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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{awaittransaction.runUpdate({sql:`INSERT Singers (SingerId, FirstName, LastName)        VALUES (11, 'Timothy', 'Campbell')`,});const[rows]=awaittransaction.run({sql:'SELECT FirstName, LastName FROM Singers',});rows.forEach(row=>{constjson=row.toJSON();console.log(`${json.FirstName}${json.LastName}`);});awaittransaction.commit();}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}});

PHP

use Google\Cloud\Spanner\SpannerClient;use Google\Cloud\Spanner\Transaction;/** * Writes then reads data inside a Transaction with a DML statement. * * 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 write_read_with_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $database->runTransaction(function (Transaction $t) {        $rowCount = $t->executeUpdate(            'INSERT Singers (SingerId, FirstName, LastName) '            . " VALUES (11, 'Timothy', 'Campbell')");        printf('Inserted %d row(s).' . PHP_EOL, $rowCount);        $results = $t->execute('SELECT FirstName, LastName FROM Singers WHERE SingerId = 11');        foreach ($results as $row) {            printf('%s %s' . PHP_EOL, $row['FirstName'], $row['LastName']);        }        $t->commit();    });}

Python

# 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)defwrite_then_read(transaction):# Insert record.row_ct=transaction.execute_update("INSERT INTO Singers (SingerId, FirstName, LastName) "" VALUES (11, 'Timothy', 'Campbell')")print("{} record(s) inserted.".format(row_ct))# Read newly inserted record.results=transaction.execute_sql("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11")forresultinresults:print("FirstName:{}, LastName:{}".format(*result))database.run_in_transaction(write_then_read)

Ruby

# 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=0client.transactiondo|transaction|row_count=transaction.execute_update("INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')")puts"#{row_count} record updated."transaction.execute("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11").rows.eachdo|row|puts"#{row[:FirstName]}#{row[:LastName]}"endend

Get the query plan

You canretrieve a query planusing the Google Cloud console, the client libraries, and thegcloudcommand-line tool.

Use Partitioned DML

Partitioned DML is designed for bulk updatesand deletes, particularly periodic cleanup and backfilling.

Execute statements with the Google Cloud CLI

To execute a Partitioned DML statement, use thegcloud spanner databases execute-sql command with the--enable-partitioned-dml option. The following example updates rows in theAlbums table.

gcloudspannerdatabasesexecute-sqlexample-db\--instance=test-instance--enable-partitioned-dml\--sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'

Note: Spanner does not support--query-mode=PLAN and--query-mode=PROFILE for Partitioned DML.

Modify data using the client library

The following code example updates theMarketingBudget column of theAlbumstable.

C++

You use theExecutePartitionedDml() function to execute a Partitioned DML statement.

voidDmlPartitionedUpdate(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoresult=client.ExecutePartitionedDml(spanner::SqlStatement("UPDATE Albums SET MarketingBudget = 100000""  WHERE SingerId > 1"));if(!result)throwstd::move(result).status();std::cout <<"Updated at least " <<result->row_count_lower_bound            <<" row(s) [spanner_dml_partitioned_update]\n";}

C#

You use theExecutePartitionedUpdateAsync() method to execute a Partitioned DML statement.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassUpdateUsingPartitionedDmlCoreAsyncSample{publicasyncTask<long>UpdateUsingPartitionedDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1");longrowCount=awaitcmd.ExecutePartitionedUpdateAsync();Console.WriteLine($"{rowCount} row(s) updated...");returnrowCount;}}

Go

You use thePartitionedUpdate() method to execute a Partitioned DML statement.

import("context""fmt""io""cloud.google.com/go/spanner")funcupdateUsingPartitionedDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"}rowCount,err:=client.PartitionedUpdate(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) updated.\n",rowCount)returnnil}

Java

You use theexecutePartitionedUpdate() method to execute a Partitioned DML statement.

staticvoidupdateUsingPartitionedDml(DatabaseClientdbClient){Stringsql="UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";longrowCount=dbClient.executePartitionedUpdate(Statement.of(sql));System.out.printf("%d records updated.\n",rowCount);}

Node.js

You use therunPartitionedUpdate() method to execute a Partitioned DML statement.

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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);try{const[rowCount]=awaitdatabase.runPartitionedUpdate({sql:'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1',});console.log(`Successfully updated${rowCount} records.`);}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}

PHP

You use theexecutePartitionedUpdate() method to execute a Partitioned DML statement.

use Google\Cloud\Spanner\SpannerClient;/** * Updates sample data in the database by partition with a DML statement. * * This updates the `MarketingBudget` column which must be created before * running this sample. You can add the column by running the `add_column` * sample or by running this DDL statement against your database: * *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 * * Example: * ``` * update_data($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function update_data_with_partitioned_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $rowCount = $database->executePartitionedUpdate(        'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1'    );    printf('Updated %d row(s).' . PHP_EOL, $rowCount);}

Python

You use theexecute_partitioned_dml() method to execute a Partitioned DML statement.

# 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)row_ct=database.execute_partitioned_dml("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1")print("{} records updated.".format(row_ct))

Ruby

You use theexecute_partitioned_update() method to execute a Partitioned DML statement.

# 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=client.execute_partition_update("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1")puts"#{row_count} records updated."

The following code example deletes rows from theSingers table, based on theSingerId column.

C++

voidDmlPartitionedDelete(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autoresult=client.ExecutePartitionedDml(spanner::SqlStatement("DELETE FROM Singers WHERE SingerId > 10"));if(!result)throwstd::move(result).status();std::cout <<"Deleted at least " <<result->row_count_lower_bound            <<" row(s) [spanner_dml_partitioned_delete]\n";}

C#

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Threading.Tasks;publicclassDeleteUsingPartitionedDmlCoreAsyncSample{publicasyncTask<long>DeleteUsingPartitionedDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();usingvarcmd=connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");longrowCount=awaitcmd.ExecutePartitionedUpdateAsync();Console.WriteLine($"{rowCount} row(s) deleted...");returnrowCount;}}

Go

import("context""fmt""io""cloud.google.com/go/spanner")funcdeleteUsingPartitionedDML(wio.Writer,dbstring)error{ctx:=context.Background()client,err:=spanner.NewClient(ctx,db)iferr!=nil{returnerr}deferclient.Close()stmt:=spanner.Statement{SQL:"DELETE FROM Singers WHERE SingerId > 10"}rowCount,err:=client.PartitionedUpdate(ctx,stmt)iferr!=nil{returnerr}fmt.Fprintf(w,"%d record(s) deleted.",rowCount)returnnil}

Java

staticvoiddeleteUsingPartitionedDml(DatabaseClientdbClient){Stringsql="DELETE FROM Singers WHERE SingerId > 10";longrowCount=dbClient.executePartitionedUpdate(Statement.of(sql));System.out.printf("%d records deleted.\n",rowCount);}

Node.js

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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);try{const[rowCount]=awaitdatabase.runPartitionedUpdate({sql:'DELETE FROM Singers WHERE SingerId > 10',});console.log(`Successfully deleted${rowCount} records.`);}catch(err){console.error('ERROR:',err);}finally{// Close the database when finished.database.close();}

PHP

use Google\Cloud\Spanner\SpannerClient;/** * Delete sample data in the database by partition with a DML statement. * * This updates the `MarketingBudget` column which must be created before * running this sample. You can add the column by running the `add_column` * sample or by running this DDL statement against your database: * *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 * * Example: * ``` * update_data($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function delete_data_with_partitioned_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $rowCount = $database->executePartitionedUpdate(        'DELETE FROM Singers WHERE SingerId > 10'    );    printf('Deleted %d row(s).' . PHP_EOL, $rowCount);}

Python

# 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)row_ct=database.execute_partitioned_dml("DELETE FROM Singers WHERE SingerId > 10")print("{} record(s) deleted.".format(row_ct))

Ruby

# 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=client.execute_partition_update("DELETE FROM Singers WHERE SingerId > 10")puts"#{row_count} records deleted."

Use batch DML

If you need to avoid the extra latency incurred from multiple serial requests,use batch DML to send multipleINSERT,UPDATE, orDELETE statements in asingle transaction:

C++

Use theExecuteBatchDml() function to execute a list of DML statements.

voidDmlBatchUpdate(google::cloud::spanner::Clientclient){namespacespanner=::google::cloud::spanner;autocommit_result=client.Commit([&client](spanner::Transactionconst&txn)->google::cloud::StatusOr<spanner::Mutations>{std::vector<spanner::SqlStatement>statements={spanner::SqlStatement("INSERT INTO Albums"" (SingerId, AlbumId, AlbumTitle,"" MarketingBudget)"" VALUES (1, 3, 'Test Album Title', 10000)"),spanner::SqlStatement("UPDATE Albums"" SET MarketingBudget = MarketingBudget * 2""  WHERE SingerId = 1 and AlbumId = 3")};autoresult=client.ExecuteBatchDml(txn,statements);if(!result)returnstd::move(result).status();// Note: This mutator might be re-run, or its effects discarded, so// changing non-transactional state (e.g., by producing output) is,// in general, not something to be imitated.for(std::size_ti=0;i <result->stats.size();++i){std::cout <<result->stats[i].row_count <<" rows affected"                    <<" for the statement " <<(i+1) <<".\n";}// Batch operations may have partial failures, in which case// ExecuteBatchDml returns with success, but the application should// verify that all statements completed successfullyif(!result->status.ok())returnresult->status;returnspanner::Mutations{};});if(!commit_result)throwstd::move(commit_result).status();std::cout <<"Update was successful [spanner_dml_batch_update]\n";}

C#

Use theconnection.CreateBatchDmlCommand() method to create your batchcommand, use theAdd method to add DML statements, and execute thestatements with theExecuteNonQueryAsync() method.

usingGoogle.Cloud.Spanner.Data;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;publicclassUpdateUsingBatchDmlCoreAsyncSample{publicasyncTask<int>UpdateUsingBatchDmlCoreAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";usingvarconnection=newSpannerConnection(connectionString);awaitconnection.OpenAsync();SpannerBatchCommandcmd=connection.CreateBatchDmlCommand();cmd.Add("INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget) VALUES (1, 3, 'Test Album Title', 10000)");cmd.Add("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 3");IEnumerable<long>affectedRows=awaitcmd.ExecuteNonQueryAsync();Console.WriteLine($"Executed {affectedRows.Count()} "+"SQL statements using Batch DML.");returnaffectedRows.Count();}}

Go

Use theBatchUpdate() method to execute an array of DMLStatement objects.

import("context""fmt""io""cloud.google.com/go/spanner")funcupdateUsingBatchDML(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{stmts:=[]spanner.Statement{{SQL:`INSERT INTO Albums(SingerId, AlbumId, AlbumTitle, MarketingBudget)VALUES (1, 3, 'Test Album Title', 10000)`},{SQL:`UPDATE AlbumsSET MarketingBudget = MarketingBudget * 2WHERE SingerId = 1 and AlbumId = 3`},}rowCounts,err:=txn.BatchUpdate(ctx,stmts)iferr!=nil{returnerr}fmt.Fprintf(w,"Executed %d SQL statements using Batch DML.\n",len(rowCounts))returnnil})returnerr}

Java

Use thetransaction.batchUpdate() method to execute anArrayList ofmultiple DMLStatement objects.

staticvoidupdateUsingBatchDml(DatabaseClientdbClient){dbClient.readWriteTransaction().run(transaction->{List<Statement>stmts=newArrayList<Statement>();Stringsql="INSERT INTO Albums "+"(SingerId, AlbumId, AlbumTitle, MarketingBudget) "+"VALUES (1, 3, 'Test Album Title', 10000) ";stmts.add(Statement.of(sql));sql="UPDATE Albums "+"SET MarketingBudget = MarketingBudget * 2 "+"WHERE SingerId = 1 and AlbumId = 3";stmts.add(Statement.of(sql));long[]rowCounts;try{rowCounts=transaction.batchUpdate(stmts);}catch(SpannerBatchUpdateExceptione){rowCounts=e.getUpdateCounts();}for(inti=0;i <rowCounts.length;i++){System.out.printf("%d record updated by stmt %d.\n",rowCounts[i],i);}returnnull;});}

Node.js

Usetransaction.batchUpdate() to execute a list of DML statements.

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * 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);constinsert={sql:`INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget)    VALUES (1, 3, "Test Album Title", 10000)`,};constupdate={sql:`UPDATE Albums SET MarketingBudget = MarketingBudget * 2    WHERE SingerId = 1 and AlbumId = 3`,};constdmlStatements=[insert,update];try{awaitdatabase.runTransactionAsync(asynctransaction=>{const[rowCounts]=awaittransaction.batchUpdate(dmlStatements);awaittransaction.commit();console.log(`Successfully executed${rowCounts.length} SQL statements using Batch DML.`,);});}catch(err){console.error('ERROR:',err);throwerr;}finally{// Close the database when finished.database.close();}

PHP

UseexecuteUpdateBatch() to create a list of DML statements, then usecommit() to execute the statements.

use Google\Cloud\Spanner\SpannerClient;use Google\Cloud\Spanner\Transaction;/** * Updates sample data in the database with Batch DML. * * This requires the `MarketingBudget` column which must be created before * running this sample. You can add the column by running the `add_column` * sample or by running this DDL statement against your database: * *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64 * * Example: * ``` * update_data_with_batch_dml($instanceId, $databaseId); * ``` * * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. */function update_data_with_batch_dml(string $instanceId, string $databaseId): void{    $spanner = new SpannerClient();    $instance = $spanner->instance($instanceId);    $database = $instance->database($databaseId);    $batchDmlResult = $database->runTransaction(function (Transaction $t) {        $result = $t->executeUpdateBatch([            [                'sql' => 'INSERT INTO Albums '                . '(SingerId, AlbumId, AlbumTitle, MarketingBudget) '                . "VALUES (1, 3, 'Test Album Title', 10000)"            ],            [                'sql' => 'UPDATE Albums '                . 'SET MarketingBudget = MarketingBudget * 2 '                . 'WHERE SingerId = 1 and AlbumId = 3'            ],        ]);        $t->commit();        $rowCounts = count($result->rowCounts());        printf('Executed %s SQL statements using Batch DML.' . PHP_EOL,            $rowCounts);    });}

Python

Usetransaction.batch_update() to execute multiple DML statement strings.

fromgoogle.rpc.code_pb2importOK# 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)insert_statement=("INSERT INTO Albums ""(SingerId, AlbumId, AlbumTitle, MarketingBudget) ""VALUES (1, 3, 'Test Album Title', 10000)")update_statement=("UPDATE Albums ""SET MarketingBudget = MarketingBudget * 2 ""WHERE SingerId = 1 and AlbumId = 3")defupdate_albums(transaction):status,row_cts=transaction.batch_update([insert_statement,update_statement])ifstatus.code!=OK:# Do handling here.# Note: the exception will still be raised when# `commit` is called by `run_in_transaction`.returnprint("Executed{} SQL statements using Batch DML.".format(len(row_cts)))database.run_in_transaction(update_albums)

Ruby

Usetransaction.batch_update to execute multiple DML statement strings.

# 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_counts=nilclient.transactiondo|transaction|row_counts=transaction.batch_updatedo|b|b.batch_update("INSERT INTO Albums "\"(SingerId, AlbumId, AlbumTitle, MarketingBudget) "\"VALUES (1, 3, 'Test Album Title', 10000)")b.batch_update("UPDATE Albums "\"SET MarketingBudget = MarketingBudget * 2 "\"WHERE SingerId = 1 and AlbumId = 3")endendstatement_count=row_counts.countputs"Executed#{statement_count} SQL statements using Batch DML."

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-17 UTC.

Movatterモバイル変換

Insert, update, and delete data using data manipulation language (DML) Stay organized with collections Save and categorize content based on your preferences.

Use DML

Locking

Concurrency

Transaction limits

Run statements in the Google Cloud console

Execute statements with the Google Cloud CLI

Modify data using the client library

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

GoogleSQL

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

Modify data with the returning DML statements

GoogleSQL

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

PostgreSQL

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

GoogleSQL

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

PostgreSQL

C++

C#

Go

Java

Node.js

PHP

Python

Ruby

GoogleSQL

Insert, update, and delete data using data manipulation language (DML)