This topic describes the syntax used to update data in a spatio-temporal table.
Syntax
upsert_statement ::= UPSERT [hint_clause] INTO table_name
'('columns| _l_ts_')'
VALUES '(' values ')'
[ ON DUPLICATE KEY update_column = update_value |IGNORE ]?
columns ::= column_name, columns
values ::= term, values,'(' values ')'
| function_name '(' term ( ',' term )* ')'
update_column ::= column_name
update_value ::= term
hint_clause::=/*+hint_items*/
hint_items::=hint_item(','hint_item)*
hint_item::=identifier('('hint_option(','hint_option)*')')
hint_option::=expressionThe UPSERT statement can insert multiple rows in a batch. You must specify the columns to which the values are inserted. You can use the Values keyword to determine the number of rows that you want to insert.
The UPSERT ON DUPLICATE KEY operation is supported. This operation is similar to the CHECK AND PUT operation. Make sure that the specified values can identify a row. If you specify the column that you want to update and the new value after ON DUPLICATE KEY, and the column exists in the table, an exception occurs. This is because ON DUPLICATE KEY cannot update values by checking whether the specified column exists. ON DUPLICATE KEY IGNORE is used to skip the duplicate column check and directly insert rows.
You can use standard Java Database Connectivity (JDBC) to submit regular SQL statements or prepared statements to Lindorm. You can submit prepared statements by using standard JDBC to perform standard batch insert operations only in Lindorm 2.2.16 and later versions.
Examples
You can use one of the following two methods to write a piece of data into a spatio-temporal table:
Method 1
UPSERT INTO dt(id, g, t) VALUES(0,ST_MakePoint(0.0,1.0),1000);Method 2
UPSERT INTO dt(id, g, t) VALUES(0,ST_GeomFromText('POINT (0.0 1.0)'),1000);
You can execute the following statement to write multiple pieces of data into a spatio-temporal table at a time:
UPSERT INTO dt (id, g, t) VALUES(1,ST_MakePoint(1.23,2.34),2000),(2,ST_MakePoint(3.45,5.67),3000),(3,ST_MakePoint(4.56,5.67),4000);
For more information about the spatio-temporal functions that can be used to write data, see Overview.
For more examples on how to use JDBC to write data, see Getting started.