A Guide to INSERT, UPDATE, and DELETE Statements in Oracle

In this article, we’ll be focusing on three of the most used SQL commands in Oracle: INSERT, UPDATE, and DELETE. These commands are used to insert new data into tables, update existing data, and delete data from tables, respectively.

In this article I will take a closer look at each of these statements and how they are used in Oracle. Please note the INSERT, UPDATE, and DELETE statements can only be used on tables that you have been granted the privilege to execute these commands.

Note that by default, Oracle implicitly starts a transaction after every statement. Because of this, you must use the COMMIT or ROLLBACK statements to manage these transactions after you attempt to make your changes to the contents of a table (the transaction starts even if no data is modified, like if you have a WHERE clause that does not return FALSE for any rows.) For more detail on Oracle transactions, see Transactions in the Oracle documentation)

A transaction is a sequence of one or more SQL statements that are treated as a single unit of work. Once you have made your changes, you need to execute one of the following statements:

• COMMIT: This statement is used to permanently save the changes made during a transaction. When you issue a COMMIT statement, all the changes made during the transaction are made permanent and cannot be undone. After the COMMIT statement is executed, a new transaction begins.
• ROLLBACK: This statement is used to undo the changes made during a transaction. When you issue a ROLLBACK statement, all the changes made during the transaction are undone, and the database is restored to its previous state. After the ROLLBACK statement is executed, a new transaction begins.

It’s important to use COMMIT and ROLLBACK statements carefully to ensure data integrity. If a transaction is not committed, the changes made during the transaction will not be visible to other users or applications and could hinder their work. If a transaction is rolled back, any changes made during the transaction will be lost.

Here’s an example of an "Employee" table: You can use the following SQL script to create the "Employee" table. We will use this table to learn to insert, update and delete commands.

Note: In our following CREATE TABLE Statement, we utilized the Oracle NOT NULL constraint for Name, LastName, and Dob columns, which ensures that a column cannot store NULL values. These constraints are inline and commonly included in the column definition of a CREATE TABLE statement. When you define one or more columns as the primary key for a table, it automatically includes the NOT NULL constraint, which prohibits NULL values from being inserted into that column. If you try to insert a NULL value into a primary key column, or one defined as NOT NULL, Oracle SQL will raise an error.

Additionally in Oracle SQL, a default constraint sets a value that is automatically assigned to a column when a new row is inserted into a table and a value for that column is not specified. The default value is specified when the table is created using the DEFAULT keyword. we have defined the DEFAULT value 'Unknown' for the "Department" column.

SQL script to create the "Employee" table.

CREATE TABLE Employee (
    EmployeeId INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    Department VARCHAR(50) DEFAULT 'Unknown',
    Dob DATE NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL 
);

The INSERT Statement

In this section I will cover the typical uses of the INSERT statement. The Oracle INSERT statement is very similar to other RDBMS platforms, but it also has several, quite useful differences.

INSERT Statement Basic Syntax:

The SQL INSERT statement is used to add new data to a database. There are different types of SQL INSERT statements, each with its syntax and usage. The basic INSERT statement is used to insert a single row of data into a table, while the other types are used for more complex data insertion operations.

The basic INSERT command is used to add new data to a table in Oracle. The syntax for this command is as follows:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

In this syntax, table_name is the name of the table you want to insert data into, and column1 through columnN are the names of the columns you want to insert data into. The VALUES keyword is followed by a comma-separated list of values that correspond to the columns you specified earlier.

In this section, we will cover the following different ways of writing SQL INSERT statements.

• Single row INSERT statement.
• INSERT ALL statements.
• INSERT from SELECT statement.

Single row INSERT statement

A single-row INSERT statement inserts a single row into a table. Let’s consider an example to understand this statement better. Using the table named "Employee" as defined at the beginning of the article, to insert a new employee row into this table, you can use the following INSERT statement:

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName,
      Department, Dob, Salary)
VALUES ('1000', 'Allen Cox', 'Cox', 'Allen', 'IT', 
      TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000);
COMMIT;

This statement will add a new row to the "Employee" table with the specified values.

Now let’s see what happens if we don’t specify the "Department" column in our INSERT statement because we already defined a default value for the "Department" column in the CREATE TABLE statement.

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName,
      Dob, Salary)
VALUES ('1001', 'Allen Cox', 'Cox', 'Allen',  
     TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000); 

COMMIT;

You will notice that the "Department" column has the default value 'Unknown', which is automatically assigned because we omitted the column from the INSERT statement.

Output:

Note: It is not required to specify the columns in the INSERT statement. So, the following is allowed:

INSERT INTO Employee 
VALUES ('1001', 'Allen Cox', 'Cox', 'Allen',  
     TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000); 

COMMIT;

This will be covered later in the article in more detail, but you should always specify the columns for the INSERT statement in code that is meant to be executed sometime in the future. The time savings of not including the list of columns is so much less than when there are changes to the underlying table and the code breaks and must be supported.

Inserting Multiple Rows at a time

In this section, I will cover the multiple ways that you can insert more than a single row at a time.

Multiple Batches

Perhaps this is obvious, but one straightforward way is to insert more than a few rows, is to create a script with multiple inserts in a batch. The script above will put five records into the Employee table.

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 
                      Department, Dob, Salary)
VALUES ('1002', 'Dave Carry', 'Carry',  'Dave', 'Sales', 
       TO_DATE('01-Jan-1992', 'DD-MM-YYYY'),70000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 
                      Department, Dob, Salary)
VALUES ('1003', 'Amit Singh','Singh',   'Amit', 'Sales', 
       TO_DATE('20-Oct-1991', 'DD-MM-YYYY'),50000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 
                      Department, Dob, Salary) 
VALUES ('1004', 'Rhonda Grant', 'Grant',        'Rhonda', 
       'Marketing', 
       TO_DATE('15-May-1980', 'DD-MM-YYYY'),60000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 
                      Department, Dob, Salary) 
VALUES ('1005', 'Marvin Cox', 'Cox',    'Marvin', 'Marketing', 
       TO_DATE('15-Jan-1985', 'DD-MM-YYYY'),62000);

INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, 
                      Department, Dob, Salary) 
VALUES ('1006', 'Dave Grant', 'Grant',  'Dave', 'Sales', 
        TO_DATE('05-Oct-1980', 'DD-MM-YYYY'),90000);

COMMIT;

Rerun the SELECT statement using the "Employee" table now looks like this:

If there are errors in any of the rows, you can ROLLBACK the transaction and none of the new rows would be created.

INSERT ALL statement

The INSERT ALL statement in Oracle allows you to insert multiple rows into one or more tables using a single statement. It is useful when you need to insert data into multiple tables based on a single source of data.

Syntax:

INSERT ALL
    INTO table1 (column1, column2, ...) 
    VALUES (value1, value2, ...)

    INTO table2 (column1, column2, ...) 
    VALUES (value1, value2, ...)

    INTO table3 (column1, column2, ...) 
    VALUES (value1, value2, ...)

Subquery;

In the original statement, it is required that each value expression value1, value2, or value3 refers to a column returned by the select list of the subquery. It is important to note that the column names and values must match in each INTO and VALUES clause. Also, the number of columns and their data types must be compatible between the INTO clause and the corresponding VALUES clause.

However, to use literal values instead of values returned by the subquery, you can utilize the following subquery:

SELECT * FROM dual;

Here’s an example:

Suppose we have two tables: "Employee" and "Address". The "Employee" table is the same as the one we created at the beginning of the article. The "Address" table is as follows:

CREATE TABLE Address (
   EmployeeId NUMBER PRIMARY KEY,
   Address_Line1 VARCHAR2(100),
   Address_Line2 VARCHAR2(100),
   City VARCHAR2(50),
   State VARCHAR2(50),
   Country VARCHAR2(50)
);

Let’s say we want to insert data into both tables using a single INSERT ALL statement. We can do this as follows:

INSERT ALL

  INTO Employee (EmployeeId, Name, LastName, FirstName,
              Department, Dob, Salary)
  VALUES ('1007', 'Ram Singh', 'Singh', 'Ram', 'IT', 
          TO_DATE('08-July-1982', 'DD-MM-YYYY'),75000)
 
  INTO Address (EmployeeId, Address_Line1, City, 
                State, Country) 
  VALUES ('1007', '123 Main St', 'Anytown', 'CA', 'USA')

  SELECT * FROM dual; 

COMMIT;

In this example, we are inserting data into two tables, "Employee" and "Address". The SELECT * FROM dual statement at the end is required because the INSERT ALL statement requires a subquery (in this case a SELECT statement) at the end.

The first INTO clause specifies that we want to insert data into the "Employee" table, and the second INTO clause specifies that we want to insert data into the "Address" table. The SELECT statement at the end specifies the data we want to insert.

After executing this statement, the "Employee" table would contain the following row:

And the "Address" table would contain the following row:

INSERT ALL subquery example

Suppose we have two tables, "Name" and "Salary" and we want to insert records into both tables using a single INSERT ALL statement. We’ll INSERT the employee’s salary and name details from the "Employee" table.

You can create "Name" and "Salary" table using the following code:

CREATE TABLE Name (
  EmployeeId NUMBER PRIMARY KEY,
  LastName VARCHAR(50) NOT NULL,
  FirstName VARCHAR(50) NOT NULL,
  Dob DATE NOT NULL
);

CREATE TABLE Salary (
  EmployeeId NUMBER PRIMARY KEY,
  Salary DECIMAL (10, 2) NOT NULL 
);

Let’s now write an INSERT statement to insert the data.

INSERT ALL

    INTO Name   (EmployeeId, LastName , FirstName , Dob)
         VALUES (EmployeeId, LastName , FirstName , Dob)

    INTO Salary (EmployeeId , Salary)
         VALUES (EmployeeId , Salary)

SELECT EmployeeId, LastName , FirstName , Dob, Salary
FROM Employee ;

The data in the “Name” table appears as shown after executing the INSERT ALL statement mentioned above.

Output of the "Salary" Table:

INSERT from SELECT statement

The INSERT INTO SELECT statement is used to insert data into a table from another table. This can be useful when you need to copy data from one table to another or when you need to modify the data in some way before inserting it into the new table.

The syntax for the INSERT INTO SELECT statement is as follows:

INSERT INTO table_name (column1, column2, ..., columnN) 
SELECT column1, column2, ..., columnN 
FROM source_table 
WHERE condition;

Just like for the single-row usage of the INSERT statement, table_name is the name of the table you want to insert data into, and column1 through columnN are the names of the columns you want to insert data into.

The SELECT keyword is followed by a list of columns from the source table, and the FROM keyword is followed by the name of the source table. The WHERE keyword is optional and allows you to filter the data before inserting it into the new table.

For example, the following SQL statement would copy all rows from the "Employee" table where the "Department" column is "Sales" into a new table called "Sales_Employee".

Use the following SQL to create the "Sales_Employee" table.

CREATE TABLE Sales_Employee (
    EmployeeId  varchar(15) Primary key,
    Name varchar(15),
    Dob DATE,
    Salary NUMBER(8,0)
);

Use the following SQL to insert the data into the "Sales_Employee" table.

INSERT INTO Sales_Employee(EmployeeId, Name, Dob, Salary) 
SELECT EmployeeId, Name, Dob, Salary 
FROM Employee
WHERE Department = 'Sales';

COMMIT;

The SQL statement above will put three records into the "Sales_Employee" table. The output of the SELECT statement from the "Sales_Employee" table looks like this.

Insert Data Without Using an Explicit Column List

This brings us to a discussion of INSERT statements that do not specify a column list. It is a useful technique when doing quick, ad-hoc work, but it has limitations.

In previous examples, we provided values for all columns of the table. This approach allows you to choose only the columns you wish to create data for (and let the NULL and/or default constraints handle the other columns.) If you do not include all columns, you must ensure that the values are in the same order as the columns are declared in the table.

In this case, the INSERT INTO syntax would be as follows:

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

COMMIT;

So for example, you could write the following statement to add rows from “Employee” into the “Sales_Employee” table:

INSERT INTO Sales_Employee 
SELECT EmployeeId, Name, Dob, Salary From Employee
WHERE Department = 'Sales';

COMMIT;

This is a valid way to write an INSERT statement. It is generally a good practice to specify the column list when inserting data into a table in Oracle SQL. By specifying the column list, you explicitly state which columns the data is being inserted into, and in which order.

This can help prevent errors and ensure that the data is inserted into the correct columns. While this may not seem like a big deal, if the structure of the table changes and you execute this statement, the best case is that you receive an error. Worst case data is inserted that makes no sense.

There are several reasons why it is considered good practice to specify the column list:

• Clarity: By specifying the column list, you make it clear which columns are being populated with data. This can make your code more readable and easier to understand.
• Maintenance: Specifying the column list can make it easier to maintain your code in the long term. If you or another developer need to modify the code or data structures later, it will be easier to understand and modify if the column list is explicitly stated.
• Performance: In some cases, not specifying the column list can result in slower performance. If you do not specify the column list, Oracle SQL will have to perform additional work to determine which columns the data should be inserted into. This can result in slower insert times, especially for large datasets.

There are some cases where not specifying the column list may be acceptable. For example, if you are inserting data into a table with a very small number of columns, and the order of the columns is unlikely to change, you may be able to omit the column list without much risk.

In general, it is recommended to always specify the column list when inserting data into a table in Oracle SQL, unless you have a good reason not to.

UPDATE Statement:

The UPDATE statement is used in Oracle SQL for modifying data in a table. It allows you to change the values of one or more columns in one or more rows of a table based on specified conditions.

UPDATE Syntax:

The basic syntax of the UPDATE statement is as follows:

UPDATE table_name 
SET column1 = scalar_expression1, 
    column2 = scalar_expression2,
     ..., 
    columnN = scalar_expression2 
WHERE condition;

In this syntax:

• table_name is the name of the table you want to update.
• column_name is the name of the column you want to update.
• scalar_expression is the expression that calculates the new value for the column. This can be a constant, a function call, a column reference, an arithmetic expression, or a string expression.
• condition is an optional condition that specifies which rows to update. If omitted, all rows in the table will be updated. Let’s consider an example to understand this statement better.

Examples:

Suppose you want to update the salary of the employee with an “EmployeeId" of 1002 to 80000.

Before executing the UPDATE statement, the data in the "Employee" table looks like the following:

You can use the following UPDATE statement.

UPDATE Employee 
SET Salary = 80000 
WHERE EmployeeId = 1002;

COMMIT;

After executing the UPDATE statement, the data in the "Employee" table will look like the following as we can see the Salary column is updated to 80000.

As an example of updating multiple columns, let’s edit “Salary", “Department", and “LastName" column values using a single UPDATE statement. Before executing the UPDATE statement, the data in the "Employee" table looks like the following:

UPDATE Employee 
SET Salary = 70000, 
    Department='Sales',
    LastName='Brown' 
WHERE EmployeeId = 1002;

COMMIT;

After running the UPDATE statement, you would notice that the “Salary", “Department", and “LastName" column values for "EmployeeId" 1002 have been updated to this.

Now let’s use an arithmetic expression to update the "Salary" column in the "Employee" table:

Before executing the UPDATE statement, the data in the "Employee" table looks like the following:

Now run the following UPDATE statement:

UPDATE Employee
SET Salary = Salary * 1.1
WHERE department = 'Sales';

COMMIT;

We are using an arithmetic expression to update the salary column of all employees in the Sales department. The SET clause sets the "Salary" column to the current value of salary multiplied by 1.1, effectively giving all sales employees a 10% raise.

Update using a subquery:

The following statement will update the "Salary" column of all rows in the "Employee" table where the "Department" column equals 'Sales' to the average salary of employees in the Sales department.

UPDATE Employee
SET Salary = (
  SELECT AVG(Salary)
  FROM Employees
  WHERE Department = 'Sales'
)
WHERE Department = 'Sales';

COMMIT;

After running the UPDATE statement, you would notice the following change in the Salary Column of table “Employee”.

Tips for Using the UPDATE Statement

1. Pay attention to the WHERE clause: Use the WHERE clause to specify which rows you want to update. This will help to improve performance and prevent accidental updates to the wrong rows. If you omit the WHERE clause, you risk updating all rows in the table.
2. Keep backups of your data: Be careful when using the UPDATE statement in a production environment. Always make sure you have a backup of your data before performing any modification operations. This can help you recover your data in case of a mistake or error.
3. Use subqueries carefully: Subqueries can be a powerful tool when working with the UPDATE statement, as they allow you to update data based on the results of another query. However, subqueries can also be slow and resource-intensive, especially if they are used to update a large number of rows. Use subqueries carefully and only when necessary.
4. Test your UPDATE statement first: Before running an UPDATE statement on a production database, it is important to test it on a smaller dataset to ensure that it is working correctly. This can help you identify any syntax errors or other issues before you make changes to your live data.

DELETE Statement:

The DELETE statement is used to remove one or more rows from a table. The DELETE statement is utilized for managing data in a database, and it is essential to understand how to use it effectively.

DELETE Basic Syntax:

DELETE FROM table_name 
WHERE condition;

In this syntax, ‘table_name’ is the name of the table from which you want to delete rows, and ‘condition’ is an expression that evaluates to true or false for each row in the table. If the condition is true for a row, that row will be deleted.

For example, the following SQL statement would delete all rows from the “Employee” table where the “Department” column is ‘Sales’.

DELETE FROM Employee 
WHERE Department = 'Sales';

COMMIT;

If you want to delete all rows from a table, you can omit the WHERE clause:

DELETE FROM table_name;

This statement will delete all rows from the specified table.

For example, the following SQL statement would delete all rows from the “Employee” table.

DELETE FROM Employee;

COMMIT;

Deleting Data using a subquery

In some cases, you may need to delete rows based on values in another table. Oracle allows you to do this using the DELETE statement with a subquery. The subquery is used to specify which rows to delete from the target table.

The syntax for deleting data from multiple tables using a subquery is as follows:

DELETE FROM table_name1 
WHERE column_name IN (SELECT column_name 
                      FROM table_name2 
                      WHERE condition);

In this syntax, table_name1 is the target table, and table_name2 is the source table. The subquery in parentheses is used to select the rows to delete from the target table based on the specified condition.

For example, the following SQL statement would delete all employees from the “Employee” table whose Salary is greater than 60000 in the "Sales_Employee" table.

DELETE FROM Employee 
WHERE Employeeid IN (SELECT Employeeid 
                     FROM Sales_Employee 
                     WHERE SALARY > 60000);

COMMIT;

Tips for Using the DELETE Statement

1. Pay attention to the WHERE clause to specify the conditions for deleting rows. If you omit the WHERE clause, you risk deleting all rows in the table. (Which is quite bad if not what you expected to do.)
2. It’s important to exercise caution when using the DELETE statement to remove a large number of rows in a production environment. Always make sure you have a backup of your data before performing any deletion operations.
3. Use the TRUNCATE statement instead of DELETE when you need to delete all rows from a table. TRUNCATE is much faster than DELETE for this purpose. (Note that TRUNCATE has limitations and security differences to DELETE, for more detail on TRUNCATE, check out the Oracle documentation here.)

Summary

In this article, we have looked at the usage of SQL commands, INSERT, UPDATE, and DELETE in Oracle, one of the most widely used relational database management systems. The article focuses on the basic syntax and types of SQL INSERT statements in Oracle, including single row and inserts from SELECT statements with examples.

Additionally, it covers the basic syntax of UPDATE and DELETE statements in Oracle. These commands help in manipulating the data in the database efficiently. We learned to add new data, update existing data, and delete data from a table, respectively followed by some tips.

 

The post A Guide to INSERT, UPDATE, and DELETE Statements in Oracle appeared first on Simple Talk.

from Simple Talk https://ift.tt/TLVmfW4
via