Inserting New Rows Into Database Tables

The Oracle SQL command for inserting rows into a table is the INSERT statement. Primarily, it adds one row at a time. The basic syntax of the INSERT statement is as follows:

INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...);

Here's a breakdown of the syntax elements:

• INSERT INTO: This clause is used to specify the table where you want to insert data.
• table_name: Replace this with the name of the table into which you want to insert data.
• (column1, column2, column3, ...): Specify the columns in the table where you want to insert data. If you're inserting data into all columns, you can omit this part.
• VALUES: This keyword is followed by a list of values enclosed in parentheses. The order of the values should correspond to the order of the columns specified earlier.
• (value1, value2, value3): Provide the values to be inserted into the respective columns. Ensure that the data types and order of values match the columns specified earlier.

Let's look at an example of an INSERT statement. We're going to add a row to the my_pokemon table. First, let's describe the table so that we can see the columns - we'll need to know the names data types, and/or any constraints specified in the columns, so that we can build our INSERT statement.

The table has three columns: pokemon_id, pokemon_level, and caught_at. The pokemon_id and pokemon_level take numeric values, whereas caught_at takes timestamp value as its input. Currently, we don't have any specific constraints on the table. 

With the information, we can add a row into the table as follows:

Since we're inserting a row to all the columns in the table, we can omit the column list in the INSERT statement:

When attempting to insert a row with certain missing values, it is necessary to explicitly state the column names to which you are assigning values. The column names listed in the INSERT statement does not necessarily have to match their storage order. However, the specification of column names must align with the values in the INSERT statement. For example:

While you're not required to have column names, when you insert rows into all columns in the table, I would recommend you to explicitly list column names in the statement. One of the reasons is that this approach will keep your code executable, even if the table structure is altered later. For example, let's say that you add one more column in the table. In this situation, your code without column list will no longer be executable. This is because Oracle now expects one more value to be listed in the INSERT statement VALUES clause. However, if you have enumerated column lists in the INSERT statement, your code will still be executable. It will leave the new column value NULL, but at least Oracle will not throw an error.

Data Type Conversion

Values listed in the INSERT statement must be compatible to the column data types. Otherwise, Oracle will throw a syntax error. While it is advisable to avoid any implicit type conversions, Oracle will automatically perform type conversion whenever feasible. For example, in case where you input a character string, such as '254', into a data type like NUMBER, Oracle execute an implicit data type conversion, transforming the character string to a number and the statement will be successful. For example:

Of course, it cannot convert a character string like 'Pikachu' into a number. But, again, relying on implicit data type conversion is strongly discouraged and you should avoid it as much as possible. This is not only because implicit type conversion can lead to unexpected results and errors in your code, but also make your code less readable.

INSERT and Constraints

In practice, many tables include constraints, and if you happen to have any value violating any constraint, it may result in a runtime error. To illustrate, consider a table below:

In the table creation, I added a NOT NULL constraint to pokemon_id column. Then I intently violated the constraint in the INSERT statement shown below:

In Oracle SQL, a multi-table insert is used when you want to insert data into more than one table using a single INSERT statement. This can be achieved using the INSERT ALL statement. Here's the basic syntax: 

INSERT ALL
    INTO table1 (column1, column2, ...)
    INTO table2 (column1, column2, ...)
    INTO table3 (column1, column2, ...)
    ...
Subquery; 

Here's a breakdown of the syntax elements:

• INSERT ALL: This signals the start of a multi-table insert.
• INTO table1 (column1, column2, ...): Specifies the first target table and the corresponding column-value pairs.
• INTO table2 (column1, column2, ...): Specifies the second target table and its column-value pairs.
• INTO table3 (column1, column2, ...): Specifies the additional target tables and their respective column-value pairs.
• Subquery: This is a SELECT statement that provides rows for the INSERT ALL statement.

The multi-table INSERT statement is a variation on the INSERT statement syntax. A multi-table INSERT statement repeats the INTO clause of the INSERT statement to insert data into more than one table. Each INTO clause applies to just one table, but by repeating it, you can add data to multiple tables at a time. 

The multi-table INSERT must have a subquery to select rows for inserting. Data retrieved by the subquery will be inserted into each table listed in each table in the INTO clause. That is, the enumerated columns in each INTO clause and the rows retrieved by the subquery must corresponds to each other. To illustrate, let's consider a scenario shown in the figure below:

In the figure above, I created three tables with identical columns. Then we're trying to insert id and name values from the pokemon table. To achieve this, we can write a query as follows:

In line 22, I created a query selecting id and name values from the pokemon table. Then each INTO clause in line 19, 20, and 21 inserts the entire row from the subquery into each table. Thus, records stored in the newly created three tables are identical in this example.

Multi-table INSERT statements can accomplish a variety of tasks, including the following:

• Support logical archiving at any level of detail with logical decision points embedded in the INSERT statements.
• Query data from one table and insert the data into multiple tables with conditional logic, such as transforming data into a series of archive.
• Exchange data between two similar systems of different requirements - perhaps between a transaction-based application and a data warehouse optimized for analysis.
• Integrate complex queries with GROUP BY, HAVING, set operators, and more, all while moving any number of rows dynamically, distributing output into multiple data targets, and programming logical decision points to control data distribution.
• Transform data that is stored in rows and levels into a cross-tabulation output, the type you would typically see in a spreadsheet.

What we have seen in the example mentioned earlier is the unconditional multi-table INSERT statement. To fully support the tasks, you can also add conditional expressions to the query. This is called the conditional multi-table INSERT statement. The basic syntax of the conditional multi-table INSERT statement is:

INSERT {ALL | FIRST}
    WHEN expression THEN
        INTO table1 VALUES (column1, column2, ...)
    WHEN expression THEN
        INTO table2 VALUES (column1, column2, ...)
    WHEN expression THEN
        INTO table3 VALUES (column1, column2, ...)
    ...
    ELSE
        INTO tableN VALUES (column1, column2, ...) 
Subquery; 

Here's a breakdown of the syntax elements:

• INSERT {ALL | FIRST}: Specifies whether to insert all rows or stop after condition is met.
• WHEN expression THEN: Conditional clause indicating the condition that must satisfied for the subsequent INTO clause to be executed. 
• INTO tableX VALUES (column1, column2, ...): Specified the target table (tableX) and the values to be inserted into the specified columns.
• ELSE INTO tableN VALUES (column1, column2, ...): An optional ELSE clause for a default action if none of the previous conditions is true. it specifies the target table (tableN) and the values to be inserted into the specified columns.
  
• Subquery: The actual data source for the INSERT statement. 

To illustrate, let's consider a scenario depicted in the figure below:

In the SQL statement shown above, the INSERT FIRST specifies that the insert operation should stop after the first condition is met. That is, as soon as the condition specified in the first WHEN clause is evaluated to be true, Oracle will insert rows from the subquery into the corresponding table and skip the subsequent conditions. If I have used ALL instead, Oracle would have continued to evaluate every WHEN conditions, regardless of the result from each clause.

In the WHEN clauses, the conditions checks if the type1 or type2 column of the subquery has certain value. If that's the case, then Oracle will insert the id and name values selected from the subquery into the corresponding table. If none of the previous condition is true, then the ELSE clause is executed, and it inserts the values to the ash_pokemon table.

UPDATE Rows from a Table

The UPDATE statement in SQL is used to modify existing data in a database. It operates on one table at a time, allowing the modification of one or more columns for all rows in a table or selectively specified rows. Let's take a look at an example situation:

In the figure shown above, the my_pokemon table has 4 rows with some NULL values. Now, let's say that you want to update the missing values with an UPDATE statement. You can write an SQL statement as follows:

In the UPDATE statement shown above, we specified the column value to be SYSDATE, where the caught_at column value is missing. Then the corresponding rows are updated with the SYSDATE values. You might omit the WHERE clause depending on your purpose. However, please note that omitting and set a value will affect every row in the table. For example:

In this example, I intently omit the where clause. Then Oracle updated the caught_at column values for all the 4 rows in the table. So, in many practices, you may double-check if you have forgotten any row specification in the WHERE clause.

DELETE Rows from a Table

The DELETE statement is used to remove rows from tables in the database. Just as with the UPDATE statement, rows are identified by the WHERE clause of DELETE; if WHERE is omitted, DELETE removes all the rows from the table. Here's an example of a DELETE statement:

In this example, I deleted two rows whose pokemon_id value equals to 251. The rows that I want to delete from the table is specified in the WHERE clause of the DELETE statement.

Note that when DELETE identifies a row with the WHERE clause, it removes the entire row from the table, not just an individual data element from within a row. If your goal is to remove a single column value from within an existing row and retain the row in the table, do not use DELETE; you can simply use UPDATE statement to set the value to be NULL. For example:

Updating and Deleting Rows under Constraints

Just as with the INSERT statement, the UPDATE and DELETE statement should also comply with any constraints. If the results from UPDATE or DELETE statement violates any constraints on the table, the entire statements will be rejected and none of the modification will be accepted for any of the rows.

For example, let's review the table below:

The table has NOT NULL constraint on the pokemon_id column, meaning that we cannot have any missing value for any of the row for the column. So, the statement below will throw an error:

Similarly, a DELETE statement cannot violate any constraints. This particularly matters when there is a relational constraint between two tables. For example, let's add a foreign key constraint on the pokemon_moves table as follows:

In the statement above, I created a primary key-foreign key relation between the pokemon and the pokemon_moves table, meaning that the column value must be referenced from the id value from the pokemon table. Currently, the id value 1 from the pokemon table is referenced in the pokemon_moves table. In this situation, what if I try to delete a row from the pokemon table whose id equals to 1?

Since there are rows that is currently referencing the target row, we cannot delete the row form the pokemon table.

In Oracle, the MERGE statement is a powerful and flexible way to perform an "upsert" operation, which combines the functionality of both INSERT and UPDATE statements. The MERGE statement allows you to conditionally insert a row into a table or update it if it already exists, based on a specified condition. The basic syntax of the MERGE statement is as follows:

MERGE INTO target_table 
USING source_table 
ON (conditional expression) -- MUST be enclosed by parentheses!
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...)
;

Let's break down the components of the MERGE statement:

• target_table: The table that you want to modify (insert or update)
• source_table: The table or subquery providing the data that you want to merge into the target table.
• ON (conditional expression): The condition that determines whether a row in the source matches a row in the target. If the condition is true, the rows are considered "matched."
• WHEN MATCHED THEN UPDATE: Specifies the action to be taken when a match is found. You provide the columns to be updated and their new values.
• WHEN NOT MATCHED THEN INSERT: Specifies the action to be taken when no match is found. You provide the columns to be inserted and their values.

To illustrate, let's consider the following two tables:

In the my_pokemon table above, there are two rows whose pokemon_id is 252 and 254, respectively. Query in the line 2 retrieved 10 rows with id ranging from 251 to 260. Suppose that you want to update type1 and type2 in the my_pokemon table if there is any matching pokemon_id is found from the query. You may also update pokemon_level to be 5.

Among the Pokémons retrieved from the query in the line 2, there are 8 rows whose id value is not matching with any rows in the pokemon table. Let's say that you also want to insert those rows into my_pokemon table. You can perform those tasks with a MERGE statement as follows:

In this example, the my_pokemon is the target table and pokemon is the source. They are aliased by t and s, respectively. The MERGE statement compares rows based on the Pokémon ID. If a match is found, update the target table, if no match is found, it inserts a new row with the pokemon_id, caught_at, type1, and type2 from the source table.

There isn't anything you can do with MERGE that you cannot already do with some combination of those three DML statements. However, if it's possible to use MERGE as an alternative to executing two or more DML statements, then MERGE is preferable since it combines multiple DML actions into a single SQL statement, resulting in a single pass through the database. The MERGE statement is particularly useful when dealing with data synchronization or loading scenarios, where you need to ensure that your target table is kept in sync with the source data.

Let's consider a schema depicted in the ERD shown above. We see that there are one-to-many relationship between the authors and books table. Specifically, the author_id column in the books table references the authors table. In this context, we call the authors table as parent and books a child.

In a relational database, a parent table is a table that is referenced by one or more other tables through a foreign key relationship. The table referencing the primary key of the parent table is called child table. This enforces referential integrity in the database.

However, what if you want to delete a row from the parent table? Particularly, if you're trying to delete a row from the parent table that is currently referenced by its child table, Oracle will throw an error:

When you have a parent-child relationship between tables in a database, deleting a row from the parent table without appropriately handling the related rows in the child table can lead to orphaned records. Orphaned records are child records that reference a non-existent parent, and they can cause referential integrity issues and data inconsistencies. To maintain data integrity, Oracle provides options for defining the behavior when a row from the parent table is deleted.

In Oracle, the ON DELETE clause is used when defining a foreign key constraint to specify the action to be taken if a referenced row in the parent table is deleted. The ON DELETE clause supports different options to control the behavior when a referenced row is deleted. 

ON DELETE CASCADE

If ON DELETE CASCADE is specified, it means that when a referenced row in the parent table is deleted, all corresponding rows in the child table will be automatically deleted. This ensures that there are no orphaned records in the child table. The basic syntax is as follows:

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint
FOREIGN KEY (parent_id)
REFERENCES parent_table (id)
ON DELETE CASCADE
;

ON DELETE SET NULL or DEFAULT

If ON DELETE SET NULL is specified, when a referenced row in the parent table is deleted, the foreign key columns in the corresponding rows of the child table are set to NULL. This is suitable when the relationship is optional, and you want to allow child records without a parent reference. The basic syntax of ON DELETE SET NULL is:

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint
FOREIGN KEY (parent_id)
REFERENCES parent_table (id)
ON DELETE SET NULL
;

Similar to SET NULL, but sets the foreign key columns to their default values. This works when you have specified a default value in the child table. The basic syntax is as follows:

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint
FOREIGN KEY (parent_id)
REFERENCES parent_table (id)
ON DELETE SET DEFAULT
;

DELETE vs TRUNCATE

In Oracle, the DELETE statement removes rows from a table based on a specified condition. If no condition is specified, it will delete all rows. The DELETE statements basically evaluate conditions, delete rows, and fires any associated triggers at individual row-level. The DELETE statement is a DML and is transactional. Each row deleted using the DELETE statement is logged in the database redo log generation. It can be rolled back to undo changes made by the statement if a transaction is not committed. 

On the other hand, the TRUNCATE statement is a DDL (Data Definition Language) and is used to remove all rows from a table, without checking individual rows. Thus, while you cannot have any condition-based deletion, TRUNCATE is usually faster than DELETE statement, especially when dealing with large amounts of data. It also does not fire triggers associated with the table and ignores any referential integrity constraints.