INSERT

Syntax

INSERT [IGNORE] [INTO] <table_name> [(<column_name>,...)]
    [VALUES | VALUE] (expr,...),(...),...
    [ ON DUPLICATE KEY UPDATE
      <column_name> = insert_expr
        [, <column_name> = insert_expr] ... ]
    [ ON DUPLICATE KEY DELETE WHEN insert_expr ELSE UPDATE insert_expr ]

INSERT [IGNORE] [INTO] <table_name> [(col_name,...)]
    SELECT [WITH(force_random_reshuffle=1)] ...
    [ ON DUPLICATE KEY UPDATE
      <column_name> = insert_expr
        [, <column_name> = insert_expr] ... ]
    [ ON DUPLICATE KEY DELETE WHEN insert_expr ELSE UPDATE insert_expr ]

INSERT [IGNORE] [INTO] <table_name>
    SET <column_name>=expr, ...
    [ ON DUPLICATE KEY UPDATE
      <column_name> = insert_expr
        [, <column_name>= insert_expr] ... ]
    [ ON DUPLICATE KEY DELETE WHEN insert_expr ELSE UPDATE insert_expr ]

 insert_expr:
      expr
    | VALUES(<column_name>)

Remarks

• A field of type TIMESTAMP or TIMESTAMP(6) declared with the DEFAULT <value> modifier will be set to <value> if no explicit value is specified. Refer to the discussion of these types in the Data Types topic for more details.

• SingleStore supports constants, DEFAULT, or nullary builtins such as NOW(), RAND(), or UNIX_TIMESTAMP() for expressions (expr) for INSERTs.

• INSERT queries will fail if the maximum_table_memory limit has been reached.

  Refer to Addressing the Maximum Table Memory Error for more information.

• INSERT IGNORE discards rows with duplicate keys for both singleton and multi-row inserts. In this case, SingleStore discards records with duplicate keys and, without rolling back, continues inserting records with unique keys. This can speed up performance of multi-INSERT because it avoids an extra roundtrip between the aggregators and leaves.

• Multi-inserts are atomic in nature. For a multi-insert transaction, either all the rows are committed or the transaction is rolled back.

• Multi-inserts may or may not have consecutive AUTO_INCREMENT values. See AUTO_INCREMENT behavior for more information.

• When you use the IGNORE modifier, data conversion errors for a row being inserted are ignored, and the row will be inserted with default values for fields where the conversion failed.

• If the ON DUPLICATE KEY UPDATE clause is specified, and a row is to be inserted that would result in a duplicate value in a PRIMARY KEY or UNIQUE index, SingleStore will instead perform an UPDATE of the old row.

• The IGNORE modifier and ON DUPLICATE KEY UPDATE clause cannot be used in the same INSERT query, because their semantics contradict each other. For more information, see Query Errors.

• When using ON DUPLICATE KEY UPDATE, the affected row count will be 0 if no existing row is changed, 1 if a new row is inserted, and 2 if an existing row is updated.

• ON DUPLICATE KEY UPDATE cannot update unique key columns in columnstore tables. For rowstore tables, ON DUPLICATE KEY UPDATE is allowed as long as the updated column isn't part of the table's explicitly defined shard key or primary key if there's no explicitly defined shard key.

• This command must be run on the master aggregator or a child aggregator node (see Cluster Management Commands).

• INSERT ... SELECT into AUTO_INCREMENT columns can only be pushed down to leaves if the source column is also AUTO_INCREMENT, because AUTO_INCREMENT values are generated on the aggregator. This prevents insertion of 0 or NULL values in the AUTO_INCREMENT column, if the source column isn’t AUTO_INCREMENT. For more information, see AUTO_INCREMENT Behavior.

• SingleStore supports INSERT ... SELECT across databases. The column data types that are being inserted must match the column data types from selected table:

  SQL

  Copy

  INSERT INTO db2.test2.t2 (col1, col2, ...) SELECT col1, col2,... FROM db1.test1.t1;

• Writing to multiple databases in a transaction is not supported.

• Scalar expression are not supported as part of the VALUES clause:

  Copy

   INSERT INTO t1 (col1, col2) VALUES ((SELECT col1 FROM t2 LIMIT 1), 1);

• INSERT … ON DUPLICATE KEY [UPDATE|IGNORE] has a variation which instead of the optional update or ignore if there is a duplicate key, it can DELETE the duplicate.

• Refer to the Permission Matrix for the required permission.

Examples

INSERT INTO mytbl (v) VALUES ("hello"), ("goodbye");

CREATE TABLE mytbl2(a int not null);

INSERT IGNORE mytbl2 VALUES(null);

SELECT * FROM mytbl2;

+---+
| a |
+---+
| 0 |
+---+
1 row in set (0.24 sec)

INSERT INTO mytbl (column1, column2, column3)
    SELECT WITH(force_random_reshuffle=1)
    * FROM mytbl_new ON DUPLICATE KEY UPDATE
      column1 = VALUES(column1),
      column2 = VALUES(column2),
      column3 = VALUES(column3);

Query OK, 29 rows affected (0.18 sec)

CREATE TABLE viewing_stats (program_id INT PRIMARY KEY NOT NULL, view_count INT);

DELIMITER //
CREATE OR REPLACE PROCEDURE view_tally(_program_id INT, _view_count INT) AS
BEGIN
INSERT INTO viewing_stats VALUES(_program_id, _view_count)
ON DUPLICATE KEY DELETE
  WHEN view_count + values(view_count) <= 0
ELSE UPDATE view_count = view_count + _view_count;
END//
DELIMITER ;

CALL view_tally(1,1);
CALL view_tally(2,1);
CALL view_tally(3,1);
CALL view_tally(4,1);
CALL view_tally(5,1);

SELECT * FROM viewing_stats;

+------------+------------+
| program_id | view_count |
+------------+------------+
|          5 |           1|
|          4 |           1|
|          3 |           1|
|          2 |           1|
|          1 |           1|
+------------+------------+

CALL view_tally(2,1);

SELECT * FROM viewing_stats;

+------------+------------+
| program_id | view_count |
+------------+------------+
|          5 |           1|
|          4 |           1|
|          3 |           1|
|          2 |           2|
|          1 |           1|
+------------+------------+

CALL view_tally(1,-1);

SELECT * FROM viewing_stats;

+------------+------------+
| program_id | view_count |
+------------+------------+
|          5 |           1|
|          4 |           1|
|          3 |           1|
|          2 |           2|
+------------+------------+

Related Topics

• LOAD DATA

• REPLACE