Skip to content

DML syntax (INSERT UPDATE DELETE)

Jump to bottom
Knut Olav Løite edited this page May 21, 2018 · 11 revisions

Data Manipulation Language syntax

Google Cloud Spanner does not natively support DML statements (INSERT, UPDATE, DELETE). The JDBC driver therefore defines its own syntax for DML statements. Because of the limitations of the underlying Cloudspanner API, the actual updating is limited to updating single rows. The driver is however able to analyze SQL-statements and convert these into a bulk of updates on single rows. This is done by specifying a SELECT-statement as part of the INSERT- or UPDATE-statement. This SELECT-statement is used by the driver to obtain a resultset with the values that should be used for the update, and are sent back to Google Cloud Spanner as INSERT/UPDATE-statements.

INSERT

INSERT INTO _table_name_ (_column_specifications_)  
VALUES (_value_specifications_)

BULK INSERT

INSERT INTO _table_name_ (_column_specifications_)  
SELECT _value_specifications_  
FROM _table_name_  
WHERE _where_clause_  
[ON DUPLICATE KEY UPDATE]

If ON DUPLICATE KEY UPDATE is added to the end of an insert statement, the driver will generate an Insert-Or-Update statement. If a record with the same primary key value is already present in the table, the columns specified in the _column_specifications_ will be updated and no constraint violation exception will be thrown.

UPDATE

UPDATE _table_name_ SET  
_column_name_ = _value_specification_ [, _column_name_ = _value_specification_ ...]  
WHERE _primary_key_column_ = _value_specification_ [AND _primary_key_column_ = _value_specification_]

BULK UPDATE

UPDATE _table_name_ SET  
_column_name_ = _value_specification_ [, _column_name_ = _value_specification_ ...]  
WHERE _where_clause_

Alternative insert-or-update:

INSERT INTO _table_name_ (_column_specifications_)  
SELECT _value_specifications_  
FROM _table_name_  
WHERE _where_clause_  
ON DUPLICATE KEY UPDATE

DELETE

DELETE FROM _table_name_  
WHERE _primary_key_column_ = _value_specification_ [AND _primary_key_column_ = _value_specification_]

BULK DELETE

DELETE FROM _table_name_  
WHERE _where_clause_

VALUE_SPECIFICATION

When specifying a value as a literal, the notation to use depends on whether you let Google Cloud Spanner SELECT the value (server side literals) or whether you are specifying a literal client side. Client side literals are literals that are used when you specify a single row INSERT or UPDATE statement.

Client side literals

For client side literals you must use expressions supported by the SQL Parser:

  • BYTES should be specified in hex format: 0x00aa11bb
  • DATE: {d 'yyyy-mm-dd'}
  • TIMESTAMP: {ts 'yyyy-mm-dd hh:mm:ss.f . . .'} (second fractions are optional)
  • TIME: {t 'yyyy-mm-dd hh:mm:ss.f . . .'} (second fractions are optional)

Client side literals must be used in statements like these:

INSERT INTO TAB1 (COL1, COL2, COL3)
VALUES
(1, {d '2017-10-01'}, {ts '2017-10-01 10:05:10'})

UPDATE TAB1 SET
COL2={d '2017-10-01'},
COL3={ts '2017-10-01 10:05:10'}
WHERE COL1=1

Array literals

Array literals can be specified using string literals containing curly braces. String-, date-, and timestamp literals in array literals must be enclosed in double quotes. Examples:

-- INT64 array
INSERT INTO TAB1 (ARRAYCOL1)
VALUES
('{1,2,3}')

-- FLOAT64 array
INSERT INTO TAB1 (ARRAYCOL1)
VALUES
('{1.0, 2.5, 3.14159265359}')

-- BOOL array
INSERT INTO TAB1 (ARRAYCOL1)
VALUES
('{true, true, false}')

-- STRING array (note the double quotes)
INSERT INTO TAB1 (ARRAYCOL1)
VALUES
('{"one", "two", "three"}')

-- DATE array (note the double quotes)
INSERT INTO TAB1 (ARRAYCOL1)
VALUES
('{ {d "2017-10-01"}, {d "2018-05-17"}, {d "2018-05-20"} }')

-- TIMESTAMP array (note the double quotes)
INSERT INTO TAB1 (ARRAYCOL1)
VALUES
('{ {ts "2017-10-01 10:05:10"}, {ts "2018-05-20 15:45:20.12345"} }')

Server side literals

For server side literals you must use the specifications of Google Cloud Spanner: https://cloud.google.com/spanner/docs/functions-and-operators#casting
Examples:

  • DATE: CAST('2017-10-01' AS DATE)
  • TIMESTAMP: CAST('2017-10-01 10:00:00 Europe/Amsterdam' AS TIMESTAMP)

Server side literals must be used in all other statements (including bulk INSERT and UPDATE statements).

Examples:

INSERT INTO TAB1 (COL1, COL2, COL3)
SELECT COL4, CAST('2017-10-01' AS DATE), CAST('2017-10-01 10:00:00 Europe/Amsterdam' AS TIMESTAMP)
FROM TAB2
WHERE COL5<1000

UPDATE TAB1 SET
COL2=CAST('2017-10-01' AS DATE),
COL3=CAST('2017-10-01 10:00:00 Europe/Amsterdam' AS TIMESTAMP)
WHERE COL1<1000

Clone this wiki locally