All Products
Search
Document Center

INSERT

Last Updated: Jun 18, 2021

Description

You can execute the INSERT statement to add one or more records to a table.

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:
    tbl_name [PARTITION (partition_name,...)]

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

column_list
    column_name [, ...]

update_asgn_list:
     column_name = expr [, ...]

Parameters

If you execute the INSERT…ON DUPLICATE KEY UPDATE... statement, the number of affected rows is calculated based on the following rules:

  • If you do not specify the CLIENT_FOUND_ROWS flag in client_capabilities, the number of affected rows is calculated based on the following rules:
    • If a new row is inserted, one row is affected.
    • If an inserted row conflicts with an existing row in the table and the data in the table remains the same after the update, no row is affected. If the data in the table before the update is different from that after the update, two rows are affected.
  • If you specify the CLIENT_FOUND_ROWS flag, the number of affected rows is calculated based on the following rules:
    • If a new row is inserted, one row is affected.
    • If the data in the table remains the same after the update, one row is affected.
    • If the data in the table before the update is different from that after the update, two rows are affected.
  • If you do not specify the CLIENT_FOUND_ROWS flag, the number of affected rows equals the number of updated rows. If you specify the CLIENT_FOUND_ROWS flag, the number of affected rows equals the number of touched rows that conflict with existing rows. The data in the touched rows may not be modified.

Parameter

Description

IGNORE

Ignore the errors that occur when the INSERT statement is executed.

column_list

The columns to insert. Separate multiple columns with commas (,).

tbl_name

The name of the table to insert.

partition_name

The partition name of the table to insert.

update_asgn_list

Assign values to the column. For example, c1 = 2.

ON DUPLICATE KEY UPDATE

Specifies whether to update duplicate primary key or unique key values. Assume that a value you insert conflicts with an existing primary key or unique key value. In this case, if you have added ON DUPLICATE KEY UPDATE to the statement, the new value overwrites the existing value. If you do not add ON DUPLICATE KEY UPDATE, the system returns an error when duplicate primary key or unique key values exist.

Examples

The following examples are based on tables t1 and t2.

OceanBase(admin@test)>create table t1(c1 int primary key, c2 int) partition by key(c1) partitions 4;
Query OK, 0 rows affected (0.16 sec)

OceanBase(admin@test)>create table t2(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
OceanBase(admin@test)>select * from t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)
  • Insert a row into table t1.
OceanBase(admin@test)>insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.04 sec)
  • Insert multiple rows into table t1.
OceanBase(admin@test)>insert t1 values(1,1),(2,default),(2+2,3*4);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 | NULL |
|  4 |   12 |
+----+------+
3 rows in set (0.02 sec)
  • Insert a row into partition p0 in table t1.
OceanBase(admin@test)>insert into t1 partition(p0) (c1) values(5);
Query OK, 1 row affected (0.02 sec)
OceanBase(admin@test)>select * from t1 partition(p0);
+----+------+
| c1 | c2   |
+----+------+
|  5 | NULL |
+----+------+
1 row in set (0.01 sec)
  • Insert the query results from table t2 into table t1.
OceanBase(admin@test)>insert into t1 select * from t2;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

OceanBase(admin@test)>select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.01 sec)
  • Insert data into table t1 and add ON DUPLICATE KEY UPDATE to the statement to update duplicate primary key values.
OceanBase(admin@test)>insert into t1 values(1,1),(1,2) ON DUPLICATE KEY UPDATE c1=100;
Query OK, 3 rows affected (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 0

OceanBase(admin@test)>select * from t1;
+-----+------+
| c1  | c2   |
+-----+------+
| 100 |    1 |
+-----+------+
1 row in set (0.02 sec)
  • Insert data into updatable view v.
OceanBase(admin@test)>create view v as select * from t1;
Query OK, 0 rows affected (0.07 sec)
OceanBase(admin@test)>insert into v values(1,1);
Query OK, 1 row affected (0.01 sec)

OceanBase(admin@test)>select * from v;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.02 sec)

Notes

You cannot use subqueries in the INSERT statement to insert rows. For example, you cannot execute the following statement:

insert into (select * from t1) values(1, 1);