INDUSTRIAL MANUFACTURING BLOG YOU SHOULD BE READING

GoogleSQL data manipulation language | Spanner

29 Jul.,2024

GoogleSQL data manipulation language | Spanner

Sikai supply professional and honest service.

GoogleSQL is the new name for Google Standard SQL! New name, same great SQL dialect.

The GoogleSQL data manipulation language (DML) lets you update, insert, and delete data in GoogleSQL 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(),
  LastName    STRING(),
  BirthDate   DATE,
  Status      STRING(),
  LastUpdated TIMESTAMP,
  SingerInfo  googlesql.example.SingerInfo,
  AlbumInfo   googlesql.example.Album,
) PRIMARY KEY(SingerId);

CREATE TABLE AlbumInfo (
  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 AlbumInfo 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(),
  LastName   STRING(),
  BirthDate  DATE,
) PRIMARY KEY(SingerId);

CREATE TABLE Fans (
  FanId     STRING(36) DEFAULT (GENERATE_UUID()),
  FirstName STRING(),
  LastName  STRING(),
) PRIMARY KEY(FanId);

Definitions for protocol buffers used in examples

pckage googlesql.example;

message SingerInfo {
  optional string    nationality = 1;
  repeated Residence residence   = 2;

  message Residence {
    required int64  start_year   = 1;
    optional int64  end_year     = 2;
    optional string city         = 3;
    optional string country      = 4;
  }
}

message Album {
  optional string title = 1;
  optional int64 tracks = 2;
  repeated string comments = 3;
  repeated Song song = 4;

  message Song {
    optional string songtitle = 1;
    optional int64 length = 2;
    repeated Chart chart = 3;

    message Chart {
      optional string chartname = 1;
      optional int64 rank = 2;
    }
  }
}

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 by default returns the number of rows inserted into the table.

INSERT [[OR] IGNORE | UPDATE]
[INTO] table_name
 (column_name_1 [, ..., column_name_n] )
 input [return_clause]

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

return_clause:
  THEN RETURN [ WITH ACTION [ AS alias ]  ] { select_all | expression [  [ AS ] alias ] } [, ...]

select_all:
    [ table_name. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

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.
GoogleSQL 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, 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.

Note:INSERT is not supported in

is not supported in Partitioned DML

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 of INT64 is compatible.
GoogleSQL can implicitly coerce the value into the target type.

Default values

Use the DEFAULT keyword to insert the default value of a column. If a column is not included in the list, GoogleSQL assigns the default value of the column. If the column has no defined default value, NULL is assigned to the column.

The use of default values is subject to current Spanner limits, including the mutation limit. If a column has a default value and it is used in an insert or update, the column is counted as one mutation. For example, assuming that table T has three columns and that col_a has a default value, the following inserts each result in three mutations:

INSERT INTO T (id, col_a, col_b) VALUES (1, DEFAULT, 1);
INSERT INTO T (id, col_a, col_b) VALUES (2, 200, 2);
INSERT INTO T (id, col_b) VALUES (3, 3);

For more information about default column values, see the DEFAULT ( expression ) clause in CREATE TABLE.

For more information about mutations, see What are mutations?.

INSERT OR IGNORE

Use the INSERT OR IGNORE clause to insert new rows that don't exist in the table. If the primary key of the row already exists, then the row is ignored. For an INSERT OR IGNORE query that inserts multiple rows or inserts from a subquery, only the new rows are inserted. Rows where the primary key already exists are ignored.

For example, if the primary key is SingerId and the table already contains a SingerId of 7, then in the following example, INSERT would insert the first row and ignore the second row:

INSERT OR IGNORE INTO Singers
    (SingerId, FirstName, LastName, Birthdate, Status, SingerInfo)
VALUES (5, "Zak", "Sterling", "-03-12", "active", "nationality:'USA'"),
       (7, "Edie", "Silver", "-01-23", "active", "nationality:'USA'");

You can use INSERT OR IGNORE in single or batch DML requests using the executeBatchDml API.

Use the INSERT OR UPDATE clause to insert or update a row. If the primary key is not found, a new row is inserted. If a row with the primary key already exists in the table, then it is updated with the values that you specify in the statement.

For example, in the following statement, INSERT OR UPDATE modifies the column value of Status from active to inactive in the existing table with the primary key SingerId of 5.

INSERT OR UPDATE INTO Singers
    (SingerId, Status)
VALUES (5, "inactive");

If the row does not exist, the previous statement inserts a new row with values in the specified fields.

You can use INSERT OR UPDATE in single or batch DML requests using the executeBatchDml API.

THEN RETURN

Use the THEN RETURN clause to return the results of the INSERT operation and selected data from the newly inserted rows. This clause is especially useful for retrieving values of columns with default values, generated columns, and auto-generated keys, without having to use additional SELECT statements.

Use the THEN RETURN clause to capture expressions based on newly inserted rows that include the following:

WITH ACTION: An optional clause that adds a string column called ACTION to the result row set. Each value in this column represents the type of action that was applied to the column during statement execution. Values include INSERT, DELETE, and UPDATE. The ACTION column is appended as the last output column.
*: Returns all columns.
table_name.*: Returns all columns from the table. You cannot use the .* expression with other expressions, including field access.
EXCEPT ( column_name [, ...] ): Specifies the columns to exclude from the result. All matching column names are omitted from the output.
REPLACE ( expression [ AS ] column_name [, ...] ): Specifies one or more expression AS identifier clauses. Each identifier must match a column name from the table_name.* statement. In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause. Note that the value that gets inserted into the table is not replaced, just the value returned by the THEN RETURN clause.
expression: Represents a column name of the table specified by table_name or an expression that uses any combination of such column names. Column names are valid if they belong to columns of the table_name. Excluded expressions include aggregate and analytic functions.
alias: Represents a temporary name for an expression in the query.

For instructions and code samples, see Modify data with the returning DML statements.

INSERT examples

INSERT using literal values examples

The following example adds two rows to the Singers table.

INSERT INTO Singers (SingerId, FirstName, LastName, SingerInfo)
VALUES(1, 'Marc', 'Richards', "nationality: 'USA'"),
      (2, 'Catalina', 'Smith', "nationality: 'Brazil'"),
      (3, "Andrew", "Duneskipper", NULL);

These are the two new rows in the table:

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL nationality: USA NULL 2 Catalina Smith NULL NULL nationality: Brazil NULL 3 Alice Trentor NULL NULL NULL NULL

INSERT using a SELECT statement example

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 Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL NULL 3 Alice Trentor NULL NULL NULL 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 Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL NULL 3 Alice Trentor NULL NULL NULL NULL 4 Lea Martin NULL NULL NULL NULL 5 David Lomond NULL NULL NULL NULL 6 Elena Campbell NULL NULL NULL NULL

INSERT using a subquery example

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 Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL 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 Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL NULL 4 Lea NULL NULL NULL 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));

INSERT with THEN RETURN examples

The following query inserts two rows into a table, uses THEN RETURN to fetch the SingerId column from these rows, and computes a new column called FullName.


INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES
    (7, 'Melissa', 'Garcia'),
    (8, 'Russell', 'Morales')
THEN RETURN SingerId, FirstName || ' ' || LastName AS FullName;

The following table shows the query result:

SingerId FullName 7 Melissa Garcia 8 Russell Morales

The following query inserts a row to the Fans table. Spanner automatically generates a Version 4 UUID for the primary key FanId, and returns it using the THEN RETURN clause.

INSERT INTO Fans (FirstName, LastName)
VALUES ('Melissa', 'Garcia')
THEN RETURN FanId;

The following table shows the query result:

FanId 6af-f009-4c15-8c42-ebe38ae

The following query tries to insert or update a row into a table. It uses THEN RETURN to fetch the modified row and WITH ACTION to show the modified row action type.

INSERT OR UPDATE Singers (SingerId, FirstName, LastName)
VALUES (7, 'Melissa', 'Gartner')
THEN RETURN WITH ACTION SingerId, FirstName || ' ' || LastName AS FullName;

SingerId FullName Action 7 Melissa Gartner UPDATE

INSERT OR IGNORE example

The following query inserts a row in the Singers table for singers with an ID between 10 and 100. If an ID already exists in Singers, it's ignored.

INSERT OR IGNORE INTO Singers (
  SingerId, FirstName, LastName, BirthDate, Status, SingerInfo)
  (SELECT id, fname, lname, dob, status, info
    FROM latest_album
    WHERE id > 10
    AND id < 100);

DELETE statement

Use the DELETE statement to delete rows from a table.

[statement_hint_expr] DELETE [FROM] table_name [table_hint_expr] [[AS] alias] WHERE condition [return_clause];

statement_hint_expr: '@{' statement_hint_key = statement_hint_value '}'

table_hint_expr: '@{' table_hint_key = table_hint_value '}'

return_clause:
    THEN RETURN [ WITH ACTION [ AS alias ]  ] { select_all | expression [  [ AS ] alias ] } [, ...]

select_all:
    [ table_name. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

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 table_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 table_name. This alias lets you reference columns in table_name without qualifying them with table_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.

THEN RETURN

With the optional THEN RETURN clause, you can obtain data from rows that are being deleted in a table. To learn more about the values that you can use in this clause, see THEN RETURN.

Statement hints

statement_hint_expr is a statement-level hint. The following hints are supported:

statement_hint_key statement_hint_value Description PDML_MAX_PARALLELISM An integer between 1 to Sets the maximum parallelism for Partitioned DML queries.
This hint is only valid with Partitioned DML query execution mode.

Table hints

table_hint_expr is a hint for accessing the table. The following hints are supported:

table_hint_key table_hint_value Description FORCE_INDEX Index name Use specified index when querying rows to be deleted. FORCE_INDEX _BASE_TABLE Don't use an index. Instead, scan the base table.

DELETE examples

DELETE with WHERE clause example

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 Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL NULL 3 Alice Trentor NULL NULL NULL NULL

The following table shows the data after the statement is executed.

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL NULL

DELETE with subquery example

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

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 Status SingerInfo AlbumInfo 4 Lea Martin NULL NULL NULL NULL 5 David Lomond NULL NULL NULL NULL 6 Elena Campbell NULL NULL NULL NULL

DELETE with THEN RETURN example

The following query deletes all rows in a table that contains a singer called Melissa and returns all columns in the deleted rows except the LastUpdated column.

DELETE FROM Singers WHERE Firstname = 'Melissa'
THEN RETURN * EXCEPT (LastUpdated);

The following table shows the query result:

SingerId FirstName LastName BirthDate 7 Melissa Garcia NULL

Use the UPDATE statement to update existing rows in a table.

[statement_hint_expr] UPDATE table_name [table_hint_expr] [[AS] alias]
SET update_item [, ...]
WHERE condition [return_clause];

update_item: column_name = { expression | DEFAULT }

statement_hint_expr: '@{' statement_hint_key = statement_hint_value '}'

table_hint_expr: '@{' table_hint_key = table_hint_value '}'

return_clause:
    THEN RETURN [ WITH ACTION [ AS alias ]  ] { select_all | expression [  [ AS ] alias ] } [, ...]

select_all:
    [ table_name. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

Where:

table_name is the name of a table to update.
The SET clause is a list of update_items to perform on each row where the WHERE condition is true.
expression is an update expression. The expression can be a literal, a SQL expression, or a SQL subquery.
statement_hint_expr is a statement-level hint. The following hints are supported:
statement_hint_key statement_hint_value Description PDML_MAX_PARALLELISM An integer between 1 to Sets the maximum parallelism for Partitioned DML queries.
This hint is only valid with Partitioned DML query execution mode.
table_hint_expr is a hint for accessing the table. The following hints are supported:
table_hint_key table_hint_value Description FORCE_INDEX Index name Use specified index when querying rows to be updated. FORCE_INDEX _BASE_TABLE Don't use an index. Instead, scan the base table.

UPDATE statements must comply with the following rules:

Want more information on Value Square Spanner? Feel free to contact us.

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, 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 is INT64.
GoogleSQL can implicitly coerce the value into the target type.

Default values

The DEFAULT keyword sets the value of a column to its default value. If the column has no defined default value, the DEFAULT keyword sets it to NULL.

The use of default values is subject to current Spanner limits, including the mutation limit. If a column has a default value and it is used in an insert or update, the column is counted as one mutation. For example, assume that in table T, col_a has a default value. The following updates each result in two mutations. One comes from the primary key, and another comes from either the explicit value () or the default value.

UPDATE T SET col_a =  WHERE id=1;
UPDATE T SET col_a = DEFAULT WHERE id=3;

For more information about default column values, see the DEFAULT ( expression ) clause in CREATE TABLE.

For more information about mutations, see What are mutations?.

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.

THEN RETURN

With the optional THEN RETURN clause, you can obtain data from rows that are being updated in a table. To learn more about the values that you can use in this clause, see THEN RETURN.

Aliases

The WHERE clause has an implicit alias to table_name. This alias lets you reference columns in table_name without qualifying them with table_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 = '-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.

The following example updates the Singers table by updating the BirthDate column in one of the rows.

UPDATE Singers
SET BirthDate = '-10-10', SingerInfo = "nationality:'USA'"
WHERE FirstName = 'Marc' AND LastName = 'Richards';

The following table shows the data before the statement is executed.

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo 1 Marc Richards NULL NULL NULL NULL 2 Catalina Smith NULL NULL NULL NULL 3 Alice Trentor NULL NULL NULL NULL

The following table shows the data after the statement is executed.

SingerId FirstName LastName BirthDate Status SingerInfo AlbumInfo 1 Marc Richards -10-10 NULL nationality: USA NULL 2 Catalina Smith NULL NULL NULL NULL 3 Alice Trentor NULL NULL NULL NULL

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 -01-01 NULL NULL [25, 50, 100] 1 2 -01-01 NULL NULL [25, 50, 100] 2 3 -01-01 NULL NULL NULL

The following query updates all rows where the singer first name is equal to Russell and returns the SingerId in the updated rows. It also extracts the year from the updated BirthDate column as a new output column called year.

UPDATE Singers
SET BirthDate = '-10-10'
WHERE FirstName = 'Russell'
THEN RETURN SingerId, EXTRACT(YEAR FROM BirthDate) AS year;

The following table shows the query result:

SingerId year 8

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#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateUsingDmlWithStructCoreAsyncSample
{
    public async Task<int> UpdateUsingDmlWithStructCoreAsync(string projectId, string instanceId, string databaseId)
    {
        var nameStruct = new SpannerStruct
        {
            { "FirstName", SpannerDbType.String, "Timothy" },
            { "LastName", SpannerDbType.String, "Campbell" }
        };
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("UPDATE Singers SET LastName = 'Grant' WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name");
        cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) updated...");
        return rowCount;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

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

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		type name struct {
			FirstName string
			LastName  string
		}
		var singerInfo = name{"Timothy", "Campbell"}

		stmt := spanner.Statement{
			SQL: `Update Singers Set LastName = 'Grant'
				WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
			Params: map[string]interface{}{"name": singerInfo},
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

Java

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        long rowCount = transaction.executeUpdate(s);
        System.out.printf("%d record updated.\n", rowCount);
        return null;
      });
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

const nameStruct = Spanner.struct({
  FirstName: 'Timothy',
  LastName: 'Campbell',
});

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

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;

/**
 * Update data with a DML statement using Structs.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_structs(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) {
        $nameValue = (new StructValue)
            ->add('FirstName', 'Timothy')
            ->add('LastName', 'Campbell');
        $nameType = (new StructType)
            ->add('FirstName', Database::TYPE_STRING)
            ->add('LastName', Database::TYPE_STRING);

        $rowCount = $t->executeUpdate(
            "UPDATE Singers SET LastName = 'Grant' "
             . 'WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) '
             . '= @name',
            [
                'parameters' => [
                    'name' => $nameValue
                ],
                'types' => [
                    'name' => $nameType
                ]
            ]);
        $t->commit();
        printf('Updated %d row(s).' . PHP_EOL, $rowCount);
    });
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

record_type = param_types.Struct(
    [
        param_types.StructField("FirstName", param_types.STRING),
        param_types.StructField("LastName", param_types.STRING),
    ]
)
record_value = ("Timothy", "Campbell")

def write_with_struct(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Grant' "
        "WHERE STRUCT<FirstName STRING, LastName STRING>"
        "(FirstName, LastName) = @name",
        params={"name": record_value},
        param_types={"name": record_type},
    )
    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(write_with_struct)

Ruby

# 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
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Singers SET LastName = 'Grant'
     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
    params: { name: name_struct }
  )
end

puts "#{row_count} record updated."

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 Commit timestamps in GoogleSQL-dialect databases and Commit timestamps in PostgreSQL-dialect databases.

You can update non-repeating and repeating fields in protocol buffers. Consider the Singers example table. It contains a column, AlbumInfo, of type Albums, and the Albums column contains a non-repeating field tracks.

The following statement updates the value of tracks:

UPDATE Singers s
SET s.AlbumInfo.tracks = 15
WHERE s.SingerId = 5 AND s.Albums.title = "Fire is hot";

You can also update a repeated field using an array of values:

UPDATE Singers s
SET s.AlbumInfo.comments = ["A good album!", "Hurt my ears!", "Totally unlistenable."]
WHERE s.SingerId = 5 AND s.Albums.title = "Fire is Hot";

You can construct DML statements inside a parent update statement that modify a repeated field of a protocol buffer or an array. These statements are called nested updates.

For example, the Album message contains a repeated field called comments. This nested update statement adds a comment to an album:

UPDATE Singers s
SET (INSERT s.AlbumInfo.comments
     VALUES ("Groovy!"))
WHERE s.SingerId = 5 AND s.AlbumInfo.title = "Fire is Hot";

Album also contains a repeated protocol buffer, Song, which provides information about a song on the album. This nested update statement updates the album with a new song:

UPDATE Singers s
SET (INSERT s.AlbumInfo.Song(Song)
     VALUES ("songtitle: 'Bonus Track', length: 180"))
WHERE s.SingerId = 5 AND s.AlbumInfo.title = "Fire is Hot";

If the repeated field is another protocol buffer, you can provide the protocol buffer as a string literal. For example, the following statement adds a new song to the album and updates the number of tracks.

UPDATE Singers s
SET (INSERT s.AlbumInfo.Song
     VALUES ('''songtitle: 'Bonus Track', length:180''')),
     s.Albums.tracks = 16
WHERE s.SingerId = 5 and s.AlbumInfo.title = "Fire is Hot";

You can also nest a nested update statement in another nested update statement. For example, the Song protocol buffer itself has another repeated protocol buffer, Chart, which provides information on what chart the song appears on, and what rank it has.

The following statement adds a new chart to a song:

UPDATE Singers s
SET (UPDATE s.AlbumInfo.Song so
    SET (INSERT INTO so.Chart
         VALUES ("chartname: 'Galaxy Top 100', rank: 5"))
    WHERE so.songtitle = "Bonus Track")
WHERE s.SingerId = 5;

This following statement updates the chart to reflect a new rank for the song:

UPDATE Singers s
SET (UPDATE s.AlbumInfo.Song so
     SET (UPDATE so.Chart c
          SET c.rank = 2
          WHERE c.chartname = "Galaxy Top 100")
     WHERE so.songtitle = "Bonus Track")
WHERE s.SingerId = 5;

GoogleSQL treats an array or repeated field inside a row that matches an UPDATE WHERE clause as a table, with individual elements of the array or field treated like rows. These rows can then have nested DML statements run against them, allowing you to delete, update, and insert data as needed.

Modify multiple fields

The previous sections demonstrates how to update a single value in a compound data type. You can also modify multiple fields in a compound data type within a single statement. For example:

UPDATE Singers s
SET
    (DELETE FROM s.SingerInfo.Residence r WHERE r.City = 'Seattle'),
    (UPDATE s.Albums.Song song SET song.songtitle = 'No, This Is Rubbish' WHERE song.songtitle = 'This Is Pretty Good'),
    (INSERT s.Albums.Song VALUES ("songtitle: 'The Second Best Song'"))
WHERE SingerId = 3 AND s.Albums.title = 'Go! Go! Go!';

Nested queries are processed as follows:

Delete all rows that match a WHERE clause of a DELETE statement.
Update any remaining rows that match a WHERE clause of an UPDATE statement. Each row must match at most one UPDATE WHERE clause, or the statement fails due to overlapping updates.
Insert all rows in INSERT statements.

You must construct nested statements that affect the same field in the following order:

DELETE
UPDATE
INSERT

For example:

UPDATE Singers s
SET
    (DELETE FROM s.SingerInfo.Residence r WHERE r.City = 'Seattle'),
    (UPDATE s.SingerInfo.Residence r SET r.end_year =  WHERE r.City = 'Eugene'),
    (INSERT s.Albums.Song VALUES ("songtitle: 'The Second Best Song'"))
WHERE SingerId = 3 AND s.Albums.title = 'Go! Go! Go!';

The following statement is invalid, because the UPDATE statement happens after the INSERT statement.

UPDATE Singers s
SET
    (DELETE FROM s.SingerInfo.Residence r WHERE r.City = 'Seattle'),
    (INSERT s.Albums.Song VALUES ("songtitle: 'The Second Best Song'")),
    (UPDATE s.SingerInfo.Residence r SET r.end_year =  WHERE r.City = 'Eugene')
WHERE SingerId = 3 AND s.Albums.title = 'Go! Go! Go!';

In nested queries, you can't use INSERT OR REPLACE statements. These types of statements don't work because arrays and other compound data types don't always have a primary key, so there is no applicable definition of duplicate rows.

5 Ways to Perfect Your Shop's Onboarding Process

One of the best ways to grow a shop is through an onboarding process that sets up a new hire for success. Employees can't be effective assets if they're unclear on what they should do and how they should do it.

Confident employees take ownership of their new roles. With clarity, new hires can perform to your expectations and exceed them, as they&#;ll have a measure of what success looks like.

"In many instances, a shop foreman should spend at least two business days onboarding," says David Askwith, director of operations for AutoStream Car Care in Maryland. "It's important to show new hires to see the layout of the shop, introduce them to the team, show them the test drive path, and ensure they have a path for success.&#;

AutoStream Car Care has eight locations in Maryland, with 70 employees. Their average car count is 1,500 a month, with a shop size averaging 1,500 to 7,500 square feet. AutoStream Car Care's revenue is $12.5 million a year.

Here are five ways to perfect your shop's onboarding process and set up new hires for success.

1. Streamline Employee Orientation

Creating a smooth employee orientation process is essential. This is a new hire's first exposure to your shop&#;what they experience will affect their time as an employee.

Automation can help. You can create a series of videos in a company portal that trains the new hire on various aspects of their position before they set foot in the shop. You can automate some of the paperwork aspects and save time.

"We have a system and upload all our documents and training videos to software called Trainual. We make it clear what our expectations are and allow new hires to get certified," says Askwith.

How you train new hires should be kept updated and relevant to the changes in your shop. Askwith notes that who trains a new hire is essential in ensuring success. He recommends having one or a few key people train new hires.

"We try to have each new hire train at each of our locations to get a feel for how we do things. We like to touch base in each part of the process and make sure new hires know they have support," says Askwith.

2. Develop Clear Human Resources Materials

New hires should access material that allows them to read and get clarity on your shop's processes. They will have questions about their new working environment and how to handle sensitive issues.

"Shop owners must have an employee handbook which covers all the common questions and processes," says Tony Whatley, owner of 365 Driven, and co-Founder of LS1Tech, an online automotive performance community, which sold for millions.

Whatley notes that your material should cover:

Expectations around workday starting and ending times
Which holidays are off
How to request vacation time
Dress code
Work area cleanliness and order expectations
The Org chart&#;with contact info
Emergency procedures and contacts
How raises or bonuses are calculated or earned
Payroll and payment schedule
Skill, education, and certification requirements
Discipline and corrective action process
Communication process
, smoking, controlled substance, alcohol, break policies
Benefits and medical policy information

Again, automation can help distribute and have your HR material readily available for new hires (and all employees).

3. Have Defined Work Areas

As humans, we value having our own space. You may own the shop, but the goal is for new hires to take ownership of their new role&#;having their own space can help.

"Each technician should have a designated work area with room to perform duties," says Whatley. "Don't make the new person clean up the mess left by the last guy. Welcome new hires by providing them with a fresh, ready-to-use work area."

As you walk the new hire through your shop, explain who does what and where. You can have regular team meetings to ensure employees are good with their work area and allow any concerns to be raised.

4. Build in Time for Employee Socialization

Culture and community are fundamental ways to ensure each new hire feels connected to your shop. Socialization time is a great way to do that&#;especially when an employee is starting.

"We invite new hires to any team functions we have scheduled and over-communicate," says Askwith. "We want them to participate and become part of the team as soon as possible."

Regular socialization events build a strong culture and helps every employee feel more connected.

5. Invest in Your Employees' Growth

Successful shop owners invest in improving their employees. "New hires thrive when you assist them with continuing education, certifications, communication, and leadership training," says Whatley.

The best shops build in professional development, along with working on vehicles. Take care of your team, and they will be loyal. They will improve, become allies, send referrals, and become ambassadors.

"We actually train anywhere from two weeks to 30 days, with 30 days being optimal,&#; says Askwith. &#;We make sure to touch base with each new hire often to answer questions, handle any concerns, and make sure they&#;re being given everything they need to succeed.&#;

Streamlined training and orientation are just the start. The more you invest in continuing training for new hires (and all employees), the better they can become at their jobs.

Growth for new hires happens when they're equipped with tools for success. Create an onboarding process that represents the value of your shop and company.

Contact us to discuss your requirements of Non-sparking Valve Wrenches Manufacturer. Our experienced sales team can help you identify the options that best suit your needs.

Previous: How to Organize a Portable Play Therapy Kit

Next: Check Valve - Working Principle

Guest Posts