:meta-keywords: insert statement, insert set, insert select, insert vertex, insert edge, on duplicate key update, odku :meta-description: You can insert a new record into a table in a database by using the INSERT statement. CoraDB supports INSERT ... VALUES, INSERT ... SET, INSERT ... SELECT, INSERT VERTEX and INSERT EDGE statements. ****** INSERT ****** You can insert a new record into a table in a database by using the **INSERT** statement. CoraDB supports **INSERT ... VALUES**, **INSERT ... SET**, **INSERT ... SELECT**, **INSERT VERTEX** and **INSERT EDGE** statements. **INSERT ... VALUES** and **INSERT ... SET** statements are used to insert a new record based on the value that is explicitly specified while the **INSERT ... SELECT** statement is used to insert query result records obtained from different tables. Use the **INSERT VALUES** or **INSERT ... SELECT** statement to insert multiple rows by using the single **INSERT** statement. For graph tables, you can use **INSERT VERTEX** to insert records into vertex tables and **INSERT EDGE** to insert edges between vertices. :: INSERT [INTO] table_name [(column_name, ...)] {VALUES | VALUE}({expr | DEFAULT}, ...)[,({expr | DEFAULT}, ...),...] [ON DUPLICATE KEY UPDATE column_name = expr, ... ] INSERT [INTO] table_name DEFAULT [ VALUES ] INSERT [INTO] table_name SET column_name = {expr | DEFAULT}[, column_name = {expr | DEFAULT},...] [ON DUPLICATE KEY UPDATE column_name = expr, ... ] INSERT [INTO] table_name [(column_name, ...)] SELECT... [ON DUPLICATE KEY UPDATE column_name = expr, ... ] INSERT VERTEX [INTO] table_name [(column_name, ...)] {VALUES | VALUE}({expr | DEFAULT}, ...)[,({expr | DEFAULT}, ...),...] INSERT EDGE FROM (subquery) TO (subquery) INTO table_name [(column_name, ...)] [VALUES ({expr | DEFAULT}, ...)] * *table_name*: Specifies the name of the target table into which you want to insert a new record. * *column_name*: Specifies the name of the column into which you want to insert the value. If you omit to specify the column name, it is considered that all columns defined in the table have been specified. Therefore, you must specify the values for all columns next to the **VALUES** keyword. If you do not specify all the columns defined in the table, a **DEFAULT** value is assigned to the non-specified columns; if the **DEFAULT** value is not defined, a **NULL** value is assigned. * *expr* | **DEFAULT**: Specifies values that correspond to the columns next to the **VALUES** keyword. Expressions or the **DEFAULT** keyword can be specified as a value. At this time, the order and number of the specified column list must correspond to the column value list. The column value list for a single record is described in parentheses. * **DEFAULT**: You can use the **DEFAULT** keyword to specify a default value as the column value. If you specify **DEFAULT** in the column value list next to the **VALUES** keyword, a default value column is stored for the given column: if you specify **DEFAULT** before the **VALUES** keyword, default values are stored for all columns in the table. **NULL** is stored for the column whose default value has not been defined. * **ON DUPLICATE KEY UPDATE**: In case constraints are violated because a duplicated value for a column where **PRIMARY KEY** or **UNIQUE** attribute is defined is inserted, the value that makes constraints violated is changed into a specific value by performing the action specified in the **ON DUPLICATE KEY UPDATE** statement. .. code-block:: sql CREATE TABLE a_tbl1( id INT UNIQUE, name VARCHAR, phone VARCHAR DEFAULT '000-0000' ); --insert default values with DEFAULT keyword before VALUES INSERT INTO a_tbl1 DEFAULT VALUES; --insert multiple rows INSERT INTO a_tbl1 VALUES (1,'aaa', DEFAULT),(2,'bbb', DEFAULT); --insert a single row specifying column values for all INSERT INTO a_tbl1 VALUES (3,'ccc', '333-3333'); --insert two rows specifying column values for only INSERT INTO a_tbl1(id) VALUES (4), (5); --insert a single row with SET clauses INSERT INTO a_tbl1 SET id=6, name='eee'; INSERT INTO a_tbl1 SET id=7, phone='777-7777'; SELECT * FROM a_tbl1; :: id name phone ========================================================= NULL NULL '000-0000' 1 'aaa' '000-0000' 2 'bbb' '000-0000' 3 'ccc' '333-3333' 4 NULL '000-0000' 5 NULL '000-0000' 6 'eee' '000-0000' 7 NULL '777-7777' .. code-block:: sql INSERT INTO a_tbl1 SET id=6, phone='000-0000' ON DUPLICATE KEY UPDATE phone='666-6666'; SELECT * FROM a_tbl1 WHERE id=6; :: id name phone ========================================================= 6 'eee' '666-6666' .. code-block:: sql INSERT INTO a_tbl1 SELECT * FROM a_tbl1 WHERE id=7 ON DUPLICATE KEY UPDATE name='ggg'; SELECT * FROM a_tbl1 WHERE id=7; :: id name phone ========================================================= 7 'ggg' '777-7777' In **INSERT ... SET** syntax, the evaluation of an assignment expression is performed from left to right. If the column value is not specified, then the default value is assigned. If there is no default value, **NULL** is assigned. .. code-block:: sql CREATE TABLE tbl (a INT, b INT, c INT); INSERT INTO tbl SET a=1, b=a+1, c=b+2; SELECT * FROM tbl; :: a b c =================================== 1 2 4 In the above example, b's value will be 2 and c's value will be 4 since a's value is 1. .. code-block:: sql CREATE TABLE tbl2 (a INT, b INT, c INT); INSERT INTO tbl2 SET a=b+1, b=1, c=b+2; In the above example, a's value will be **NULL** since b's value is not specified yet when assigning a's value. .. code-block:: sql SELECT * FROM tbl2; :: a b c =================================== NULL 1 3 .. code-block:: sql CREATE TABLE tbl3 (a INT, b INT default 10, c INT); INSERT INTO tbl3 SET a=b+1, b=1, c=b+2; In the above example, a's value will be 11 since b's value is not specified yet and b's default is 10. .. code-block:: sql SELECT * FROM tbl3; :: a b c =================================== 11 1 3 INSERT ... SELECT Statement =========================== If you use the **SELECT** query in the **INSERT** statement, you can insert query results which satisfy the specified retrieval condition from one or many tables to the target table. :: INSERT [INTO] table_name [(column_name, ...)] SELECT... [ON DUPLICATE KEY UPDATE column_name = expr, ... ] The **SELECT** statement can be used in place of the **VALUES** keyword, or be included as a subquery in the column value list next to **VALUES**. If you specify the **SELECT** statement in place of the **VALUES** keyword, you can insert multiple query result records into the column of the table at once. However, there should be only one query result record if the **SELECT** statement is specified in the column value list. .. code-block:: sql --creating an empty table which schema replicated from a_tbl1 CREATE TABLE a_tbl2 LIKE a_tbl1; --inserting multiple rows from SELECT query results INSERT INTO a_tbl2 SELECT * FROM a_tbl1 WHERE id IS NOT NULL; --inserting column value with SELECT subquery specified in the value list INSERT INTO a_tbl2 VALUES(8, SELECT name FROM a_tbl1 WHERE name <'bbb', DEFAULT); SELECT * FROM a_tbl2; :: id name phone ========================================================= 1 'aaa' '000-0000' 2 'bbb' '000-0000' 3 'ccc' '333-3333' 4 NULL '000-0000' 5 NULL '000-0000' 6 'eee' '000-0000' 7 NULL '777-7777' 8 'aaa' '000-0000' ON DUPLICATE KEY UPDATE Clause ============================== In a situation in which a duplicate value is inserted into a column for which the **UNIQUE** index or the **PRIMARY KEY** constraint has been set, you can update to a new value by specifying the **ON DUPLICATE KEY UPDATE** clause in the **INSERT** statement. .. note:: * If **PRIMARY KEY** and **UNIQUE** or multiple **UNIQUE** constraints exist on a table together, constraint violation can happen by one of them; so in this case, **ON DUPLICATE KEY UPDATE** clause is not recommended. * Even if **UPDATE** is executed after failing executing **INSERT**, **AUTO_INCREMENT** value which is increased once cannot be rolled back into the previous value. :: INSERT ... [ON DUPLICATE KEY UPDATE column_name = expr, ... ] * *column_name* = *expr*: Specifies the name of the column whose value you want to change next to **ON DUPLICATE KEY UPDATE** and a new column value by using the equal sign. .. code-block:: sql --creating a new table having the same schema as a_tbl1 CREATE TABLE a_tbl3 LIKE a_tbl1; INSERT INTO a_tbl3 SELECT * FROM a_tbl1 WHERE id IS NOT NULL and name IS NOT NULL; SELECT * FROM a_tbl3; :: id name phone ========================================================= 1 'aaa' '000-0000' 2 'bbb' '000-0000' 3 'ccc' '333-3333' 6 'eee' '000-0000' .. code-block:: sql --insert duplicated value violating UNIQUE constraint INSERT INTO a_tbl3 VALUES(2, 'bbb', '222-2222'); :: ERROR: Operation would have caused one or more unique constraint violations. With ON DUPLICATE KEY UPDATE, "affected rows" value per row will be 1 if a new row is inserted, and 2 if an existing row is updated. .. code-block:: sql --insert duplicated value with specifying ON DUPLICATED KEY UPDATE clause INSERT INTO a_tbl3 VALUES(2, 'ggg', '222-2222') ON DUPLICATE KEY UPDATE name='ggg', phone = '222-2222'; SELECT * FROM a_tbl3 WHERE id=2; :: id name phone ========================================================= 2 'ggg' '222-2222' 2 rows affected. INSERT VERTEX Statement ======================= The **INSERT VERTEX** statement is used to insert records into vertex tables in graph databases. Vertex tables are created using the **CREATE VERTEX TABLE** statement. :: INSERT VERTEX [INTO] table_name [(column_name, ...)] {VALUES | VALUE}({expr | DEFAULT}, ...)[,({expr | DEFAULT}, ...),...] * *table_name*: Specifies the name of the vertex table into which you want to insert a new record. * *column_name*: Specifies the name of the column into which you want to insert the value. * *expr* | **DEFAULT**: Specifies values that correspond to the columns. Expressions or the **DEFAULT** keyword can be specified as a value. .. note:: * Inserting into a vertex table can be done using either **INSERT** or **INSERT VERTEX**. Both statements work identically for vertex tables. * The **INSERT VERTEX** keyword makes it explicit that you are inserting into a graph vertex table. .. code-block:: sql --creating vertex and edge tables CREATE VERTEX TABLE person (name STRING, yearOfBirth STRING); CREATE EDGE TABLE likes (rating STRING, review STRING); CREATE VERTEX TABLE movie (name STRING, type STRING); CREATE EDGE TABLE IS_PLAYING_ON; CREATE VERTEX TABLE cinema (name STRING, type STRING); CREATE EDGE TABLE LOCATED_IN (address STRING); CREATE VERTEX TABLE city (name STRING, population STRING); --inserting vertices using INSERT VERTEX INSERT VERTEX INTO person VALUES ('George', '2001'); INSERT VERTEX INTO person VALUES ('Joanna', '1890'); INSERT VERTEX INTO movie VALUES ('The Notebook', '2D'); INSERT VERTEX INTO movie VALUES ('The Hate You Give', '3D'); INSERT VERTEX INTO movie VALUES ('Titanic', '4DX'); INSERT VERTEX INTO movie VALUES ('Apollo', '2DX'); INSERT VERTEX INTO cinema VALUES ('Cinema City', 'Indoor'); INSERT VERTEX INTO cinema VALUES ('AFI Cinema', 'Outdoor'); INSERT VERTEX INTO cinema VALUES ('Happy Cinema', 'Outdoor'); INSERT VERTEX INTO cinema VALUES ('Best Cinema', 'Indoor'); INSERT VERTEX INTO city VALUES ('Bucharest', '3000000'); INSERT VERTEX INTO city VALUES ('Timisoara', '2000000'); --inserting vertices using regular INSERT (also valid for vertex tables) INSERT INTO person VALUES ('Mary', '1995'); INSERT INTO person VALUES ('John', '1988'); INSERT EDGE Statement ===================== The **INSERT EDGE** statement is used to insert edges between vertices in graph databases. Edge tables are created using the **CREATE EDGE TABLE** statement. Unlike vertex tables, edge tables **require** the **INSERT EDGE** statement; regular **INSERT** cannot be used. :: INSERT EDGE FROM (subquery) TO (subquery) INTO table_name [(column_name, ...)] [VALUES ({expr | DEFAULT}, ...)] * **FROM** (*subquery*): Specifies a subquery that returns the source vertex for the edge. The subquery must return exactly one vertex. * **TO** (*subquery*): Specifies a subquery that returns the destination vertex for the edge. The subquery must return exactly one vertex. * *table_name*: Specifies the name of the edge table into which you want to insert the edge. * *column_name*: Specifies the name of the column into which you want to insert the value for edge properties. * *expr* | **DEFAULT**: Specifies values for edge properties. If the edge table has no properties, you can use **VALUES ()** or omit the VALUES clause entirely. .. note:: * For inserting edges into edge tables, you **must** use the **INSERT EDGE** statement. Regular **INSERT** statements will not work. * Each **INSERT EDGE** statement creates exactly one edge between two vertices. * The subqueries in the **FROM** and **TO** clauses must return exactly one vertex each. If a subquery returns multiple rows or no rows, an error will occur. .. code-block:: sql --inserting edges with properties INSERT EDGE FROM (SELECT person FROM person WHERE name = 'George') TO (SELECT movie FROM movie WHERE name = 'The Notebook') INTO likes VALUES ('10 stars', 'Worst'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'Joanna') TO (SELECT movie FROM movie WHERE name = 'The Hate You Give') INTO likes VALUES ('20 stars', 'Best'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'Mary') TO (SELECT movie FROM movie WHERE name = 'Titanic') INTO likes VALUES ('25 stars', 'Good'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'John') TO (SELECT movie FROM movie WHERE name = 'Titanic') INTO likes VALUES ('15 stars', 'Bad'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'John') TO (SELECT movie FROM movie WHERE name = 'Apollo') INTO likes VALUES ('15 stars', 'Bad'); --inserting edges without properties INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'The Notebook') TO (SELECT cinema FROM cinema WHERE name = 'Cinema City') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'The Hate You Give') TO (SELECT cinema FROM cinema WHERE name = 'Cinema City') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'Titanic') TO (SELECT cinema FROM cinema WHERE name = 'Happy Cinema') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'The Hate You Give') TO (SELECT cinema FROM cinema WHERE name = 'AFI Cinema') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'Titanic') TO (SELECT cinema FROM cinema WHERE name = 'AFI Cinema') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'Apollo') TO (SELECT cinema FROM cinema WHERE name = 'Happy Cinema') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'Titanic') TO (SELECT cinema FROM cinema WHERE name = 'Best Cinema') INTO IS_PLAYING_ON VALUES (); INSERT EDGE FROM (SELECT movie FROM movie WHERE name = 'Apollo') TO (SELECT cinema FROM cinema WHERE name = 'Best Cinema') INTO IS_PLAYING_ON VALUES (); --inserting edges with properties into LOCATED_IN edge table INSERT EDGE FROM (SELECT cinema FROM cinema WHERE name = 'Cinema City') TO (SELECT city FROM city WHERE name = 'Bucharest') INTO LOCATED_IN VALUES ('address 1'); INSERT EDGE FROM (SELECT cinema FROM cinema WHERE name = 'Cinema City') TO (SELECT city FROM city WHERE name = 'Timisoara') INTO LOCATED_IN VALUES ('address 2'); INSERT EDGE FROM (SELECT cinema FROM cinema WHERE name = 'AFI Cinema') TO (SELECT city FROM city WHERE name = 'Bucharest') INTO LOCATED_IN VALUES ('address 3'); INSERT EDGE FROM (SELECT cinema FROM cinema WHERE name = 'Happy Cinema') TO (SELECT city FROM city WHERE name = 'Bucharest') INTO LOCATED_IN VALUES ('address 4'); INSERT EDGE FROM (SELECT cinema FROM cinema WHERE name = 'Happy Cinema') TO (SELECT city FROM city WHERE name = 'Timisoara') INTO LOCATED_IN VALUES ('address 5'); INSERT EDGE FROM (SELECT cinema FROM cinema WHERE name = 'Best Cinema') TO (SELECT city FROM city WHERE name = 'Timisoara') INTO LOCATED_IN VALUES ('address 6'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'George') TO (SELECT city FROM city WHERE name = 'Bucharest') INTO LOCATED_IN VALUES ('address 7'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'Joanna') TO (SELECT city FROM city WHERE name = 'Timisoara') INTO LOCATED_IN VALUES ('address 8'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'Mary') TO (SELECT city FROM city WHERE name = 'Bucharest') INTO LOCATED_IN VALUES ('address 9'); INSERT EDGE FROM (SELECT person FROM person WHERE name = 'John') TO (SELECT city FROM city WHERE name = 'Timisoara') INTO LOCATED_IN VALUES ('address 10');