Is it possible to update a spanner record based on a where parameter using SpannerConnection.CreateUpdateCommand

Question

I have currently got the following code

var cmdUpdate _spannerConnection.CreateUpdateCommand(databaseTable, updateColumns);
await cmdUpdate.ExecuteNonQueryAsync();

where update columns is a list of columns in the table to update with corresponding values. The primary key is also included in the list of columns as I think it must be clever enough to use that somehow.

It must product something like update A = 'Z', B = 'Y' where PrimaryKeyId = {primaryKeyId} and this updates my record fine

But I would now like to do something like the following

update A = 'Z', B = 'Y' where PrimaryKeyId = {primaryKeyId} and ModifiedDate = '010124'

I initially thought to use the CreateDMLCommand but reading the docs it says

CreateUpdateCommand(string, SpannerParameterCollection)etc) are preferred as they are more efficient

Ref: https://github.com/googleapis/google-cloud-dotnet/blob/main/apis/Google.Cloud.Spanner.Data/Google.Cloud.Spanner.Data/SpannerConnection.cs#L587

So is adding an extra where parmater possible when using the CreateUpdateCommand method or am I forced to use the CreateDMLCommand method?

Answer 1

Spanner supports two ways for updating data: Mutations and DML.

CreateUpdateCommand uses Mutations for updating data. This is slightly more efficient than using DML, but has the explicit limitation that it only supports updating a row based on the primary key. So using an expression like where PrimaryKeyId = {primaryKeyId} and ModifiedDate = '010124' is not supported.

Instead, you need to use a DML statement for that.