This page describes how to insert, update, and delete data using thegcloud command-line tool.

Modifying data using DML

To execute Data Manipulation Language (DML) statements, use thegcloud spannerdatabases execute-sql command . The following example adds a new row to theSingers table.

gcloudspannerdatabasesexecute-sqlexample-db--instance=test-instance\--sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"

To execute a Partitioned DML statement, use thegcloud spanner databasesexecute-sql command with the--enable-partitioned-dml option. The followingexample updates rows in theAlbums table.

gcloudspannerdatabasesexecute-sqlexample-db\--instance=test-instance--enable-partitioned-dml\--sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'

For the Spanner DML reference, seeData Manipulation Languagesyntax.

Modifying rows using the rows command group

Use thegcloud spanner rows command group to modify data in a database:

• Insert new rows into a table.
• Update columns in existing rows in a table.
• Delete rows from a table.

Therows command group recognizes literals for allvalid column types.

Insert a row in a table

To insert a new row in a table, you must include values for the key columns andany other required columns:

gcloudspannerrowsinsert--instance=INSTANCE_ID--database=DATABASE_ID\--table=TABLE_NAME\--data=COL_NAME_1=COL_VALUE_1,COL_NAME_2=COL_VALUE_2,COL_NAME_3=COL_VALUE_3,...,COL_NAME_N=COL_VALUE_N

The following example inserts a new row in theSingers table:

gcloudspannerrowsinsert--instance=test-instance--database=example-db\--table=Singers\--data=SingerId=1,FirstName='Marc',LastName='Richards'

Update a row in a table

To update a row in a table, you must include values for the key columns and thecolumns you want to update:

gcloudspannerrowsupdate--instance=INSTANCE_ID--database=DATABASE_ID\--table=TABLE_NAME\--data=COL_NAME_1=COL_VALUE_1,COL_NAME_2=COL_VALUE_2,COL_NAME_3=COL_VALUE_3,...,COL_NAME_N=COL_VALUE_N

The following example updates a row in theSingers table:

gcloudspannerrowsupdate--instance=test-instance--database=example-db\--table=Singers\--data=SingerId=1,FirstName='Marc',LastName='Richards'

You cannot change the key values using theupdate command. To update a keyvalue, you must create a new row and delete the existing row.

Delete a row from a table

To delete a row, you must specify the values for the primary key columns:

gcloudspannerrowsdelete--instance=INSTANCE_ID--database=DATABASE_ID\--table=TABLE_NAME\--keys=KEY_VALUE_1,KEY_VALUE_2,KEY_VALUE_3

gcloudspannerrowsdelete--instance=test-instance--database=example-db\--table=Singers\--keys=1

Specify ARRAY values

To insert or update values in anARRAY column, put thedata in a YAML file and use the--flags-file option.

For example, this YAML file specifies the array[1,2,3] for theNumberscolumn:

# stats.yaml--data:Id:1Locked:TrueNumbers:-1-2-3

To insert a row with the YAML data, use the--flags-file option:

gcloudspannerrowsinsert--instance=test-instance--database=example-db\--table=Stats\--flags-filestats.yaml

For aNULL array, don't include a value forNumbers in the file:

# stats.yaml--data:Id:1Locked:True

For an empty array, define the array as[]:

# stats.yaml--data:Id:1Locked:TrueNumbers:[]

Specify commit timestamps

To insert or update a value automatically in acommit timestamp column, passspanner.commit_timestamp() as the value of the column. The following examplewrites the commit timestamp in theLastUpdated column when the row isinserted.

gcloudspannerrowsinsert--instance=test-instance--database=example-db\--table=Singers\--data=SingerId=1,LastUpdated='spanner.commit_timestamp()'

The following example writes a specific timestamp value in theLastUpdatedcolumn:

gcloudspannerrowsupdate--instance=test-instance--database=example-db\--table=Singers\--data=SingerId=1,LastUpdated=2017-01-02T12:34:00.45Z