GoogleSQL data manipulation language

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

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

Tables used in examples

CREATE TABLE Singers (
  SingerId    INT64 NOT NULL,
  FirstName   STRING(1024),
  LastName    STRING(1024),
  BirthDate   DATE,
  Status      STRING(1024),
  LastUpdated TIMESTAMP,
  SingerInfo  googlesql.example.SingerInfo,
  AlbumInfo   googlesql.example.Album,
) PRIMARY KEY(SingerId);

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

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

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

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

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

Definitions for protocol buffers used in examples

package googlesql.example;

message SingerInfo {
  optional string    nationality = 1;
  repeated Residence residence   = 2;

  message Residence {
    required int64  start_year   = 1;
    optional int64  end_year     = 2;
    optional string city         = 3;
    optional string country      = 4;
  }
}

message Album {
  optional string title = 1;
  optional int64 tracks = 2;
  repeated string comments = 3;
  repeated Song song = 4;

  message Song {
    optional string songtitle = 1;
    optional int64 length = 2;
    repeated Chart chart = 3;

    message Chart {
      optional string chartname = 1;
      optional int64 rank = 2;
    }
  }
}

Notation used in the syntax

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

INSERT statement

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

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

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

expr: value_expression | DEFAULT

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

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

INSERT statements must comply with these rules:

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

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

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

Value type compatibility

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

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

Default values

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

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

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

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

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

INSERT OR IGNORE

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

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

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

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

INSERT OR UPDATE

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

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

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

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

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

THEN RETURN

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

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

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

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

INSERT examples

INSERT using literal values examples

The following example adds two rows to the Singers table.

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

These are the two new rows in the table:

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL nationality: USA NULL
2 Catalina Smith NULL NULL nationality: Brazil NULL
3 Alice Trentor NULL NULL NULL NULL

INSERT using a SELECT statement example

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

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

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

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL NULL NULL
2 Catalina Smith NULL NULL NULL NULL
3 Alice Trentor NULL NULL NULL 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 Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL NULL NULL
2 Catalina Smith NULL NULL NULL NULL
3 Alice Trentor NULL NULL NULL NULL
4 Lea Martin NULL NULL NULL NULL
5 David Lomond NULL NULL NULL NULL
6 Elena Campbell NULL NULL NULL NULL

INSERT using a subquery example

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

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

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

Singers

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL NULL NULL
2 Catalina Smith NULL NULL NULL 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 Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL NULL NULL
2 Catalina Smith NULL NULL NULL NULL
4 Lea NULL NULL NULL 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 examples

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


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

The following table shows the query result:

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

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

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

INSERT OR IGNORE example

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

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

DELETE statement

Use the DELETE statement to delete rows from a table.

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

statement_hint_expr: '@{' statement_hint_key = statement_hint_value '}'

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

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

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

WHERE clause

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

DELETE FROM table_name WHERE true;

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

Aliases

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

DELETE FROM Singers WHERE FirstName = 'Alice';

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

THEN RETURN

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

Statement hints

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

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

Table 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 example

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

DELETE FROM Singers WHERE FirstName = 'Alice';

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

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

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

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL NULL NULL
2 Catalina Smith NULL NULL NULL NULL

DELETE with subquery example

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

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

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

Singers

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo
1 Marc Richards NULL NULL NULL NULL
2 Catalina Smith NULL NULL NULL NULL
3 Alice Trentor NULL NULL NULL NULL
4 Lea Martin NULL NULL NULL NULL
5 David Lomond NULL NULL NULL NULL
6 Elena Campbell NULL NULL NULL 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 Status SingerInfo AlbumInfo
4 Lea Martin NULL NULL NULL NULL
5 David Lomond NULL NULL NULL NULL
6 Elena Campbell NULL NULL NULL NULL

DELETE with THEN RETURN example

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

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

The following table shows the query result:

SingerId FirstName LastName BirthDate
7 Melissa Garcia NULL

UPDATE statement

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

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

update_item: column_name = { expression | DEFAULT }

statement_hint_expr: '@{' statement_hint_key = statement_hint_value '}'

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

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

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

Where:

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

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

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

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

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

UPDATE statements must comply with the following rules:

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

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

Columns not included in the SET clause are not modified.

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

SET x = y, y = x

Value type compatibility

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

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

Default values

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

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

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

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

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

WHERE clause

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

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

THEN RETURN

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

Aliases

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

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

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

UPDATE examples

UPDATE with literal values example

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

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

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

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

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

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo
1 Marc Richards 1990-10-10 NULL nationality: USA NULL
2 Catalina Smith NULL NULL NULL NULL
3 Alice Trentor NULL NULL NULL NULL

UPDATE ARRAY columns example

The following example updates an ARRAY column.

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

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

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 example

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

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

The following table shows the query result:

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.

Update fields in protocol buffers

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

The following statement updates the value of tracks:

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

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

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

Nested updates

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

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

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

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

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

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

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

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

The following statement adds a new chart to a song:

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

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

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

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

Modify multiple fields

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

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

Nested queries are processed as follows:

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

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

  • DELETE
  • UPDATE
  • INSERT

For example:

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

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

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

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