Data Manipulation Language syntax

The Cloud Spanner data manipulation language (DML) enables you to update, insert, and delete data in Cloud Spanner 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);

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 returns the number of rows inserted into the table.

INSERT [INTO] target_name
 (column_name_1 [, ..., column_name_n] )
 input

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

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.
  • Cloud Spanner 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, Cloud 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.
  • Cloud Spanner can implicitly coerce the value into the target type.

Default values

Use the DEFAULT keyword to insert the default value of a column. Cloud Spanner assigns the default value of NULL to columns that are not included in the column list.

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

DELETE statement

Use the DELETE statement to delete rows from a table.

DELETE [FROM] target_name [[AS] alias] WHERE condition;

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 target_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 target_name. This alias allows you to reference columns in target_name without qualifying them with target_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.

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

UPDATE statement

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

UPDATE target_name [[AS] alias]
SET update_item [, ...]
WHERE condition;

update_item: path_expression = expression | path_expression = DEFAULT

Where:

  • target_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.
  • path_expression is a column name.
  • expression is an update expression. The expression can be a literal, a SQL expression, or a SQL subquery.

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, Cloud 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.
  • Cloud Spanner can implicitly coerce the value into the target type.

Default values

The DEFAULT keyword sets the value of a column to NULL.

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.

Aliases

The WHERE clause has an implicit alias to target_name. This alias allows you to reference columns in target_name without qualifying them with target_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

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#

public static async Task 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}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand 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...");
    }
}

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(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              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($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $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 Writing commit timestamps.