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 ofINT64
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 theWHERE
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 isINT64
. - 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#
Go
Java
Node.js
PHP
Python
Ruby
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.