GoogleSQL data manipulation language

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

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

Tables used in examples

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
  LastUpdated TIMESTAMP,
) PRIMARY KEY(SingerId);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64,
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25),
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>,
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

CREATE TABLE AckworthSingers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
) PRIMARY KEY(SingerId);

CREATE TABLE Fans (
  FanId     STRING(36) DEFAULT (GENERATE_UUID()),
  FirstName STRING(1024),
  LastName  STRING(1024),
) PRIMARY KEY(FanId);

Notation used in the syntax

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

INSERT statement

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

INSERT [[OR] IGNORE | UPDATE]
[INTO] table_name
 (column_name_1 [, ..., column_name_n] )
 input [return_clause]

input:
 VALUES (row_1_column_1_expr [, ..., row_1_column_n_expr ] )
        [, ..., (row_k_column_1_expr [, ..., row_k_column_n_expr ] ) ]
| select_query

expr: value_expression | DEFAULT

return_clause:
    THEN RETURN { select_all | expression [  [ AS ] alias ] } [, ...]

select_all:
    [ table_name. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

INSERT statements must comply with these rules:

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

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

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

Value type compatibility

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

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

Default values

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

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

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

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

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

INSERT OR IGNORE

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

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

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

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

INSERT OR UPDATE

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

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

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

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

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

THEN RETURN

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

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

  • *: Returns all columns.
  • table_name.*: Returns all columns from the table. You cannot use the .* expression with other expressions, including field access.
  • EXCEPT ( column_name [, ...] ): Specifies the columns to exclude from the result. All matching column names are omitted from the output.
  • REPLACE ( expression [ AS ] column_name [, ...] ): Specifies one or more expression AS identifier clauses. Each identifier must match a column name from the table_name.* statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause. Note that the value that gets inserted into the table is not replaced, just the value returned by the THEN RETURN clause.
  • expression: Represents a column name of the table specified by table_name or an expression that uses any combination of such column names. Column names are valid if they belong to columns of the table_name. Excluded expressions include aggregate and analytic functions.
  • alias: Represents a temporary name for an expression in the query.

THEN RETURN is not supported with the INSERT OR IGNORE or INSERT OR UPDATE clauses.

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

INSERT examples

INSERT using literal values

The following example adds three rows to the Singers table.

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES(1, 'Marc', 'Richards'),
      (2, 'Catalina', 'Smith'),
      (3, 'Alice', 'Trentor');

These are the three new rows in the table:

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

INSERT using a SELECT statement

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

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

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

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

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

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

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

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

INSERT using a subquery

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

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

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

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL

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

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
4 Lea NULL NULL

To include multiple columns, you include multiple subqueries:

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

INSERT with THEN RETURN

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


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

The following table shows the query result:

SingerId FullName
7 Melissa Garcia
8 Russell Morales

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

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

The following table shows the query result:

FanId
6af91072-f009-4c15-8c42-ebe38ae83751

INSERT OR IGNORE

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

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

DELETE statement

Use the DELETE statement to delete rows from a table.

DELETE [FROM] table_name [table_hint_expr] [[AS] alias] WHERE condition [return_clause];

table_hint_expr: '@{' table_hint_key = table_hint_value '}'

return_clause:
    THEN RETURN { select_all | expression [  [ AS ] alias ] } [, ...]

select_all:
    [ table_name. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

WHERE clause

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

DELETE FROM table_name WHERE true;

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

Aliases

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

DELETE FROM Singers WHERE FirstName = 'Alice';

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

THEN RETURN

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

Table hints

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

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

DELETE examples

DELETE with WHERE clause

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

DELETE FROM Singers WHERE FirstName = 'Alice';

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

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

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

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

DELETE with subquery

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

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

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

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

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

Singers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

DELETE with THEN RETURN

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

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

The following table shows the query result:

SingerId FirstName LastName BirthDate
7 Melissa Garcia NULL

UPDATE statement

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

UPDATE table_name [table_hint_expr] [[AS] alias]
SET update_item [, ...]
WHERE condition [return_clause];

update_item: column_name = { expression | DEFAULT }

table_hint_expr: '@{' table_hint_key = table_hint_value '}'

return_clause:
    THEN RETURN { select_all | expression [  [ AS ] alias ] } [, ...]

select_all:
    [ table_name. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

Where:

  • table_name is the name of a table to update.
  • The SET clause is a list of update_items to perform on each row where the WHERE condition is true.
  • expression is an update expression. The expression can be a literal, a SQL expression, or a SQL subquery.
  • table_hint_expr is a hint for accessing the table. The following hints are supported:

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

UPDATE statements must comply with the following rules:

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

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

Columns not included in the SET clause are not modified.

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

SET x = y, y = x

Value type compatibility

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

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

Default values

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

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

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

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

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

WHERE clause

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

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

THEN RETURN

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

Aliases

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

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

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

UPDATE examples

UPDATE with literal values

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

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

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

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

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

SingerId FirstName LastName BirthDate
1 Marc Richards 1990-10-10
2 Catalina Smith NULL
3 Alice Trentor NULL

UPDATE ARRAY columns

The following example updates an ARRAY column.

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

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

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 NULL NULL NULL NULL
1 2 NULL NULL NULL NULL
2 3 NULL NULL NULL NULL

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

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 2018-01-01 NULL NULL [25, 50, 100]
1 2 2018-01-01 NULL NULL [25, 50, 100]
2 3 2018-01-01 NULL NULL NULL

UPDATE with THEN RETURN

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

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

The following table shows the query result:

SingerId year
8 1990

Bound STRUCT parameters

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

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateUsingDmlWithStructCoreAsyncSample
{
    public async Task<int> UpdateUsingDmlWithStructCoreAsync(string projectId, string instanceId, string databaseId)
    {
        var nameStruct = new SpannerStruct
        {
            { "FirstName", SpannerDbType.String, "Timothy" },
            { "LastName", SpannerDbType.String, "Campbell" }
        };
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name");
        cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) updated...");
        return rowCount;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func updateUsingDMLStruct(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		type name struct {
			FirstName string
			LastName  string
		}
		var singerInfo = name{"Timothy", "Campbell"}

		stmt := spanner.Statement{
			SQL: `Update Singers Set LastName = 'Grant'
				WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
			Params: map[string]interface{}{"name": singerInfo},
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

Java

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

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

const nameStruct = Spanner.struct({
  FirstName: 'Timothy',
  LastName: 'Campbell',
});

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

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

def write_with_struct(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Grant' "
        "WHERE STRUCT<FirstName STRING, LastName STRING>"
        "(FirstName, LastName) = @name",
        params={"name": record_value},
        param_types={"name": record_type},
    )
    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(write_with_struct)

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.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Singers SET LastName = 'Grant'
     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
    params: { name: name_struct }
  )
end

puts "#{row_count} record updated."

Commit timestamps

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

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

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