This page describes how to insert, update, and delete data using the
gcloud
command-line tool.
Modifying data using DML
To execute Data Manipulation Language (DML) statements, use the
gcloud spanner databases execute-sql
command . The following example adds a
new row to the Singers
table.
gcloud spanner databases execute-sql example-db --instance=test-instance \ --sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"
To execute a Partitioned DML statement, use the
gcloud spanner databases execute-sql
command with the
--enable-partitioned-dml
option. The following example updates rows in the
Albums
table.
gcloud spanner databases execute-sql example-db \ --instance=test-instance --enable-partitioned-dml \ --sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'
For the Spanner DML reference, see Data Manipulation Language syntax.
Modifying rows using the rows command group
Use the gcloud 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.
The rows
command group recognizes literals for all
valid column types.
Insert a row in a table
To insert a new row in a table, you must include values for the key columns and any other required columns:
gcloud spanner rows insert --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 the Singers
table:
gcloud spanner rows insert --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 the columns you want to update:
gcloud spanner rows update --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 the Singers
table:
gcloud spanner rows update --instance=test-instance --database=example-db \ --table=Singers \ --data=SingerId=1,FirstName='Marc',LastName='Richards'
You cannot change the key values using the update
command. To update a key
value, 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:
gcloud spanner rows delete --instance=INSTANCE_ID --database=DATABASE_ID \ --table=TABLE_NAME \ --keys=KEY_VALUE_1,KEY_VALUE_2,KEY_VALUE_3
gcloud spanner rows delete --instance=test-instance --database=example-db \ --table=Singers \ --keys=1
Specify ARRAY values
To insert or update values in an ARRAY
column, put the
data in a YAML file and use the
--flags-file
option.
For example, this YAML file specifies the array [1,2,3]
for the Numbers
column:
# stats.yaml
--data:
Id: 1
Locked: True
Numbers:
- 1
- 2
- 3
To insert a row with the YAML data, use the --flags-file
option:
gcloud spanner rows insert --instance=test-instance --database=example-db \ --table=Stats \ --flags-file stats.yaml
For a NULL
array, don't include a value for Numbers
in the file:
# stats.yaml
--data:
Id: 1
Locked: True
For an empty array, define the array as []
:
# stats.yaml
--data:
Id: 1
Locked: True
Numbers: []
Specify commit timestamps
To insert or update a value automatically in a
commit timestamp column, pass
spanner.commit_timestamp()
as the value of the column. The following example
writes the commit timestamp in the LastUpdated
column when the row is
inserted.
gcloud spanner rows insert --instance=test-instance --database=example-db \ --table=Singers \ --data=SingerId=1,LastUpdated='spanner.commit_timestamp()'
The following example writes a specific timestamp value in the LastUpdated
column:
gcloud spanner rows update --instance=test-instance --database=example-db \ --table=Singers \ --data=SingerId=1,LastUpdated=2017-01-02T12:34:00.45Z