INSERT

List of Contents

This statement inserts data into the specified table. Note that columns which are not specified in the column_list are filled with NULL value. This was defined due to the characteristics of log files that were employed for data entry and storage efficiency.

Syntax:

INSERT INTO table_name [column_list] VALUES(value_list);

Example:

INSERT INTO log_data VALUES(1, 'error');

INSERT... SELECT statement

It inserts the results from the SELECT statement into the specified table. This statement is similar to other DBMS, but has differences as follows.

  1. If column value of _ARRIVAL_TIME is not specified in SELECT and INSERT column list, it uses the time value of when conducting INSERT SELECT statement.
  2. For input value of a column which is VARCHAR type, if it is greater than the maximum length of the column, it doesn't generate an error, rather it inserts the data with the maximum length of the column.
  3. If type conversion is allowed (numeric->numeric), data are inserted in accordance with columns.
  4. The ROLLBACK doesn't work if there was an error while processing.
  5. When the value of _ARRIVAL_TIME column is specified, the new value cannot be inserted if the value is the time before the specified value of the hidden column.

Syntax:

INSERT INTO table_name [column_list] SELECT statement;

Example:

mach> CREATE TABLE insert_table (id INTEGER, brand VARCHAR(60), category VARCHAR(5));
Created successfully.

mach> INSERT INTO insert_table VALUES (101, 'CK', 'bag' );
1 row(s) inserted.

mach> INSERT INTO insert_table VALUES (102, 'VeraWang', 'dress' );
1 row(s) inserted.

mach> CREATE TABLE select_table(id INTEGER, brand VARCHAR(60), category VARCHAR(5));
Created successfully.

mach> INSERT INTO select_table(_arrival_time, id, brand, category) SELECT _arrival_time, * FROM insert_table;
2 row(s) inserted.

mach> SELECT * FROM SELECT_TABLE;
ID          BRAND                                                         CATEGORY
----------------------------------------------------------------------------------------
102         VeraWang                                                      dress
101         CK                                                            bag
[2] row(s) selected.

INSERT ON DUPLICATE KEY UPDATE

It is a special syntax to be used for inserting values into volatile and lookup tables and if there is a duplicated primary key value in the table, it update the existing value.
If there is no duplicated primary key value, it inserts a new data. In order to use this statement, primary key must be specified in a volatile table.
When you have two different data, one is to be inserted and the other is to be updated, use ON DUPLICATE KEY UPDATE SET clause. You can specify data values in SET clause.

  • In SET clause, it is formed with the list of "column = value" and the list is separated by a comma.
  • In SET clause, it is not allowed to change the primary key value.

Syntax:

INSERT INTO table_name [column_list] VALUES(value_list) ON DUPLICATE KEY UPDATE [ SET (set_list) ];

Example:

INSERT INTO code_data VALUES(2) ON DUPLICATED KEY UPDATE SET code = 1;

results matching ""

    No results matching ""