Delete data

Delete individual rows from a table.

Documentation pages that include this code sample

To view the code sample used in context, see the following documentation:

Code sample

C#

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

public static async Task DeleteIndividualRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    const int singerId = 2;
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    List<Album> albums = new List<Album>
    {
        new Album { SingerId = singerId, AlbumId = 1, AlbumTitle = "Green" },
        new Album { SingerId = singerId, AlbumId = 3, AlbumTitle = "Terrified" },
    };
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete individual rows from the UpcomingAlbums table.
        await Task.WhenAll(albums.Select(album =>
        {
            var cmd = connection.CreateDeleteCommand(
                "UpcomingAlbums",
                new SpannerParameterCollection
                {
                    { "SingerId", SpannerDbType.Int64, album.SingerId },
                    { "AlbumId", SpannerDbType.Int64, album.AlbumId }
                }
            );
            return cmd.ExecuteNonQueryAsync();
        }));

        Console.WriteLine("Deleted individual rows in UpcomingAlbums.");
    }
}

public static async Task DeleteRangeOfRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete a range of rows from the UpcomingSingers table where the column key is >=3 and <5.
        var cmd = connection.CreateDmlCommand(
           "DELETE FROM UpcomingSingers WHERE SingerId >= 3 AND SingerId < 5");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted from UpcomingSingers.");
    }
}

public static async Task DeleteAllRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete remaining UpcomingSingers rows, which will also delete the remaining
        // UpcomingAlbums rows since it was defined with ON DELETE CASCADE.
        var cmd = connection.CreateDmlCommand(
           "DELETE FROM UpcomingSingers WHERE true");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted from UpcomingSingers.");
    }
}

C++

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

void DeleteData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  // Delete the albums with key (2,1) and (2,3).
  //! [make-key] [keyset-add-key]
  auto delete_albums = spanner::DeleteMutationBuilder(
                           "Albums", spanner::KeySet()
                                         .AddKey(spanner::MakeKey(2, 1))
                                         .AddKey(spanner::MakeKey(2, 3)))
                           .Build();
  //! [make-key] [keyset-add-key]

  // Delete some singers using the keys in the range [3, 5]
  //! [make-keybound-closed]
  auto delete_singers_range =
      spanner::DeleteMutationBuilder(
          "Singers", spanner::KeySet().AddRange(spanner::MakeKeyBoundClosed(3),
                                                spanner::MakeKeyBoundOpen(5)))
          .Build();
  //! [make-keybound-closed]

  // Deletes remaining rows from the Singers table and the Albums table, because
  // the Albums table is defined with ON DELETE CASCADE.
  auto delete_singers_all =
      spanner::MakeDeleteMutation("Singers", spanner::KeySet::All());

  auto commit_result = client.Commit(spanner::Mutations{
      delete_albums, delete_singers_range, delete_singers_all});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Delete was successful [spanner_delete_data]\n";
}

Go

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.


import (
	"context"
	"io"

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

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

	m := []*spanner.Mutation{
		// Delete individual rows.
		spanner.Delete("Albums", spanner.Key{2, 1}),
		spanner.Delete("Albums", spanner.Key{2, 3}),
		// Delete a range of rows where the column key is >=3 and <5.
		spanner.Delete("Singers", spanner.KeyRange{Start: spanner.Key{3}, End: spanner.Key{5}, Kind: spanner.ClosedOpen}),
		// Delete remaining Singers rows, which will also delete the remaining
		// Albums rows because Albums was defined with ON DELETE CASCADE.
		spanner.Delete("Singers", spanner.AllKeys()),
	}
	_, err = client.Apply(ctx, m)
	return err
}

Java

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

static void deleteExampleData(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();

  // KeySet.Builder can be used to delete a specific set of rows.
  // Delete the Albums with the key values (2,1) and (2,3).
  mutations.add(
      Mutation.delete(
          "Albums", KeySet.newBuilder().addKey(Key.of(2, 1)).addKey(Key.of(2, 3)).build()));

  // KeyRange can be used to delete rows with a key in a specific range.
  // Delete a range of rows where the column key is >=3 and <5
  mutations.add(
      Mutation.delete("Singers", KeySet.range(KeyRange.closedOpen(Key.of(3), Key.of(5)))));

  // KeySet.all() can be used to delete all the rows in a table.
  // Delete remaining Singers rows, which will also delete the remaining Albums rows since it was
  // defined with ON DELETE CASCADE.
  mutations.add(Mutation.delete("Singers", KeySet.all()));

  dbClient.write(mutations);
  System.out.printf("Records deleted.\n");
}

Node.js

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

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

// Instantiate Spanner table object
const albumsTable = database.table('Albums');

// Deletes individual rows from the Albums table.
try {
  const keys = [
    [2, 1],
    [2, 3],
  ];
  await albumsTable.deleteRows(keys);
  console.log('Deleted individual rows in Albums.');
} catch (err) {
  console.error('ERROR:', err);
}

// Delete a range of rows where the column key is >=3 and <5
database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: 'DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5',
    });
    console.log(`${rowCount} records deleted from Singers.`);
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Deletes remaining rows from the Singers table and the Albums table,
  // because Albums table is defined with ON DELETE CASCADE.
  try {
    // The WHERE clause is required for DELETE statements to prevent
    // accidentally deleting all rows in a table.
    // https://cloud.google.com/spanner/docs/dml-syntax#where_clause
    const [rowCount] = await transaction.runUpdate({
      sql: 'DELETE FROM Singers WHERE true',
    });
    console.log(`${rowCount} records deleted from Singers.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    await database.close();
  }
});

PHP

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\KeyRange;
use Google\Cloud\Core\Exception\GoogleException;

/**
 * Deletes sample data from the given database.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @throws GoogleException
 */
function delete_data($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    // Delete individual rows
    $albumsToDelete = $spanner->keySet([
        'keys' => [[2, 1], [2, 3]]
    ]);
    $database->delete('Albums', $albumsToDelete);

    // Delete a range of rows where the column key is >=3 and <5
    // NOTE: A KeyRange must include a start and end.
    // NOTE: startType and endType both default to KeyRange::TYPE_OPEN.
    $singersRange = $spanner->keyRange([
        'startType' => KeyRange::TYPE_CLOSED,
        'start' => [3],
        'endType' => KeyRange::TYPE_OPEN,
        'end' => [5]
    ]);
    $singersToDelete = $spanner->keySet([
        'ranges' => [$singersRange]
    ]);
    $database->delete('Singers', $singersToDelete);

    // Delete remaining Singers rows, which will also delete the remaining
    // Albums rows because Albums was defined with ON DELETE CASCADE
    $remainingSingers = $spanner->keySet([
        'all' => true
    ]);
    $database->delete('Singers', $remainingSingers);

    print('Deleted data.' . PHP_EOL);
}

Python

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

def delete_data(instance_id, database_id):
    """Deletes sample data from the given database.

    The database, table, and data must already exist and can be created using
    `create_database` and `insert_data`.
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    # Delete individual rows
    albums_to_delete = spanner.KeySet(keys=[[2, 1], [2, 3]])

    # Delete a range of rows where the column key is >=3 and <5
    singers_range = spanner.KeyRange(start_closed=[3], end_open=[5])
    singers_to_delete = spanner.KeySet(ranges=[singers_range])

    # Delete remaining Singers rows, which will also delete the remaining
    # Albums rows because Albums was defined with ON DELETE CASCADE
    remaining_singers = spanner.KeySet(all_=True)

    with database.batch() as batch:
        batch.delete("Albums", albums_to_delete)
        batch.delete("Singers", singers_to_delete)
        batch.delete("Singers", remaining_singers)

    print("Deleted data.")

Ruby

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

# 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

# Delete individual rows
client.delete "Albums", [[2, 1], [2, 3]]

# Delete a range of rows where the column key is >=3 and <5
key_range = client.range 3, 5, exclude_end: true
client.delete "Singers", key_range

# Delete remaining Singers rows, which will also delete the remaining
# Albums rows because Albums was defined with ON DELETE CASCADE
client.delete "Singers"

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser