INSERT

Purpose

You can use this statement to add one or more records to a table.

Direct INSERT operations on subqueries are not supported. For example, INSERT INTO (SELECT * FROM t1) VALUES(1, 1) cannot be executed.

Syntax

INSERT [IGNORE] [INTO]
       single_table_insert
       [ON DUPLICATE KEY UPDATE update_asgn_list]

single_table_insert:
    {dml_table_name values_clause
     | dml_table_name '(' ')' values_clause
     | dml_table_name '(' column_list ')' values_clause
     | dml_table_name SET update_asgn_list}

dml_table_name:
    table_name [PARTITION (partition_name,...)]

values_clause:
     {{VALUES | VALUE} ({expr | DEFAULT},...) [, ...]
      | select_stmt}

column_list
    column_name [, ...]

update_asgn_list:
     column_name = expr [, ...]

Parameters

Examples

Sample tables and their data are defined as follows:

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected

obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set

• Insert a row of data into table t1.

  obclient>  INSERT INTO t1 VALUES(1,1);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  +----+------+
  1 row in set
  

• Insert multiple rows of data into table t1.

  obclient> INSERT t1 VALUES(1,1),(2,DEFAULT),(2+2,3*4);
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 | NULL |
  |  4 |   12 |
  +----+------+
  3 rows in set
  

• Insert a single row of data into partition p0 of table t1.

  obclient> INSERT INTO t1 PARTITION(p0) (c1) VALUES(5);
  Query OK, 1 row affected
  obclient> SELECT * FROM t1 PARTITION(p0);
  +----+------+
  | c1 | c2   |
  +----+------+
  |  5 | NULL |
  +----+------+
  1 row in set
  

• When you insert a duplicate primary key value into table t1, use the ON DUPLICATE KEY UPDATE function to update the value.

  obclient> INSERT INTO t1 VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE c1=100;
  Query OK, 3 rows affected
  Records: 2  Duplicates: 1  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +-----+------+
  | c1  | c2   |
  +-----+------+
  | 100 |    1 |
  +-----+------+
  1 row in set
  

• Insert values into an updatable view v.

  obclient> CREATE VIEW v AS SELECT * FROM t1;
  Query OK, 0 rows affected
  obclient> INSERT INTO v VALUES (1,1);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM v;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  +----+------+
  1 row in set
  

• Insert the query results of table t1 into table t2.

  obclient> INSERT INTO t1 SELECT * FROM t2 WHERE c1>2;
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  2 rows in set