insert_stmt ::= 'INSERT INTO' table_name ( '(' insert_column_list ')' )? 'METADATA'? 'VALUES' '(' value_list ')' ( 'ON DUPLICATE KEY UPDATE' ( 'SET' set_list )? )?
insert_column_list ::= column_name ( ',' column_name )*
value_list ::= value ( ',' value )*
set_list ::= column_name '=' value ( ',' column_name '=' value )*

create table test (number int,name varchar(20));
Created successfully.
insert into test values (1,"test");
1 row(s) inserted.
insert into test(name,number) values ("test",2);
1 row(s) inserted.

INSERT ON DUPLICATE KEY UPDATE

Machbase supports syntax similar to the commonly known UPSERT function.

A special syntax that can be used when entering a value into a Lookup/Volatile table with a primary key specified. If the table already contains data with a duplicate primary key value, the value of the existing data is changed. 
Of course, when there is no duplicated key value data, it is inserted as new data.

To use this syntax, the primary key must be specified in the volatile table.

If the column value of the inserted data is different from the column value of the updated data, or if it is desired to update a column value other than the column value of the inserted data, the SET clause can be further input.

• The SET clause consists of 'column = value', each separated by a comma.

• You must not change the default key value in the SET clause.

INSERT SELECT

insert_select_stmt:

insert_select_stmt ::= 'INSERT INTO' table_name ( '(' insert_column_list ')' )? select_stmt

This statement inserts the result of the SELECT statement on a specific table. In basic, it is similar to other DBMSs, but with the following differences.

1. The _ARRIVAL_TIME column value is entered as the time value at the time the INSERT SELECT statement is executed unless specified in the select and INSERT column lists.

2. If the input value to be inserted for a VARCHAR type column is greater than the maximum length of the column, the maximum length of the corresponding column is entered without error.

3. If type conversion is possible (numeric -> numeric), it is inserted according to the input column value.

4. ROLLBACK does not occur if an error occurs during execution.

5. If you insert a value in the _ARRIVAL_TIME column, the new value will not be entered if it has a time before the existing value.

create table t1 (i1 integer, i2 varchar(60), i3 varchar(5));
Created successfully.

insert into t1 values (1, 'a', 'ddd' );
1 row(s) inserted.
insert into t1 values (2, 'kkkkkkkkkkkkkkkkkkkkk', 'c');
1 row(s) inserted.

insert into t1 select * from t1;
2 row(s) inserted.
create table t2 (i1 integer, i2 varchar(60), i3 varchar(5));

insert into t2 (_arrival_time, i1, i2, i3) select _arrival_time, * from t1;
4 row(s) inserted.

UPDATE

update_stmt:

update_expr_list:

update_expr:

update_stmt ::= 'UPDATE' table_name ( 'METADATA' )? 'SET' update_expr_list 'WHERE' primary_key_column '=' value
update_expr_list ::= update_expr ( ',' update_expr)*
update_expr ::= column '=' value

INSERT ON DUPLICATE UPDATE syntax is also provided, rather than UPSERT via a KEY UPDATE.
You can also use the Primary Key to enter values ​​into the specified Lookup/Volatile table. In the WHERE clause, you must create a matching predicate for the primary key.

UPDATE METADATA

Only for the TAGDATA table, when you want to update the metadata.

UPDATE TAG METADATA SET ...

DELETE

delete_stmt:

time_unit:

delete_stmt ::= 'DELETE FROM' table_name ( 'OLDEST' number 'ROWS' | 'EXCEPT' number ( 'ROWS' | time_unit ) | 'BEFORE' datetime_expression )? 'NO WAIT'?
time_unit ::= 'DURATION' number time_unit ( ( 'BEFORE' | 'AFTER' ) number time_unit )?

The DELETE statement in Machbase can be performed on the log table. In addition, it is not possible to delete data in an arbitrary position in the middle, and it is possible to erase consecutively from the arbitrary position to the last (oldest log) record.

This is a policy that takes advantage of the characteristics of log data. It is a DB format representation of the act of deleting a file in order to secure space when it is entered once.

The syntax of DURATION, OLDEST, and EXCEPT cannot be used for TAG and Rollup tables.

-- Delete all.
DELETE FROM devices;

-- Delete oldest last N rows.
DELETE FROM devices OLDEST N ROWS;

-- Delete all except recent N rows.
DELETE FROM devices EXCEPT N ROWS;

-- Delete all except N matches from now on.
DELETE FROM devices EXCEPT N DAY;

-- Delete all data from before June 1, 2014.
DELETE FROM devices BEFORE TO_DATE('2014-06-01', 'YYYY-MM-DD');

-- Delete tag data from before June 1, 2014.
DELETE FROM tag BEFORE TO_DATE('2014-06-01', 'YYYY-MM-DD');

-- Delete tag rollup data from before June 1, 2014.
DELETE FROM tag ROLLUP BEFORE TO_DATE('2014-06-01', 'YYYY-MM-DD');

DELETE WHERE

delete_where_stmt:

delete_where_stmt ::= 'DELETE FROM' table_name 'WHERE' column_name '=' value

create volatile table t1 (i1 int primary key, i2 int);
Created successfully.
insert into t1 values (2,2);
1 row(s) inserted.
delete from t1 where i1 = 2;
1 row(s) deleted.

• You can only delete records that match the conditions created in the WHERE clause, which can only be performed on volatile tables.

• The primary key can only be performed on the specified volatile table.

• The WHERE clause allows only conditions of (primary key column) = (value), and can not be created with other conditions.

• You can not use a column other than the primary key column in the condition.

delete_from_tag_where_stmt:

delete_from_tag_where_stmt ::= 'DELETE FROM' table_name 'WHERE' tag_name '=' value ( and tag_time '<' datetime_expression  )?

Tag 테이블은 아래와 같이 2가지 방식의 삭제 쿼리가 추가적으로 지원된다.

• Tag name 기준 삭제
• Tag name과 Tag time 기준 삭제

-- tag name 기준 삭제
DELETE FROM tag where tag_name = 'my_tag_2021'

-- tag name 와 tag time 기준 삭제
DELETE FROM tag where tag_name = 'my_tag_2021' and tag_time < TO_DATE('2021-07-01', 'YYYY-MM-DD');

• 삭제 쿼리가 실행된 후에, 삭제된 row가 저장공간에서 물리적으로 삭제되기 까지 걸리는 시간은, DBMS의 동작상황에 따라서 다를 수 있다.

LOAD DATA INFILE

load_data_infile_stmt:

load_data_infile_stmt: 'LOAD DATA INFILE' file_name 'INTO TABLE' table_name ( 'TABLESPACE' tbs_name )? ( 'AUTO' ( 'BULKLOAD' | 'HEADUSE' | 'HEADUSE_ESCAPE' ) )? ( ( 'FIELDS' | 'COLUMNS' ) ( 'TERMINATED BY' char )? ( 'ENCLOSED BY' char )? )? ( 'TRIM' ( 'ON' | 'OFF' ) )? ( 'IGNORE' number ( 'LINES' | 'ROWS' ) )? ( 'MAX_LINE_LENGTH' number )? ( 'ENCODED BY' coding_name )? ( 'ON ERROR' ( 'STOP' | 'IGNORE' ) )?

CSV 포맷의 데이터 파일을 서버에서 직접 읽어서, 옵션에 따라 서버에서 직접 테이블 및 컬럼들을 생성하여 이를 입력하는 기능이다.

각 옵션에 대해서 설명하면 다음과 같다.

-- default field delimiter(,)  field encloser (") 를 사용하여 데이터를 입력한다.
LOAD DATA INFILE '/tmp/aaa.csv' INTO TABLE Sample_data ; 

-- 하나의 컬럼을 갖는 NEWTABLE을 생성해서 한 라인을 한 컬럼으로 입력한다. 
LOAD DATA INFILE '/tmp/bbb.csv' INTO TABLE NEWTABLE AUTO BULKLOAD; 

-- csv의 첫번째 라인을 컬럼 정보로 이용하여 NEWTABLE을 생성하고, 이를 그 테이블에 입력한다. 
LOAD DATA INFILE '/tmp/bbb.csv' INTO TABLE NEWTABLE AUTO HEADUSE; 
 
-- 첫번째 라인은 무시하고 필드 구분자는 ; enclosing 문자는 ' 로 지정해서 입력한다. 
LOAD DATA INFILE '/tmp/ccc.csv' INTO TABLE Sample_data FIELDS TERMINATED BY ';' ENCLOSED BY '\''  IGNORE 1 LINES ON ERROR IGNORE;