{"id":96835,"date":"2023-06-09T14:28:56","date_gmt":"2023-06-09T14:28:56","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96835"},"modified":"2026-05-20T14:04:08","modified_gmt":"2026-05-20T14:04:08","slug":"a-guide-to-insert-update-and-delete-statements-in-oracle","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/a-guide-to-insert-update-and-delete-statements-in-oracle\/","title":{"rendered":"Oracle INSERT, UPDATE &amp; DELETE: Syntax and Examples"},"content":{"rendered":"<p><strong>Oracle\u2019s INSERT, UPDATE, and DELETE statements are the core DML (Data Manipulation Language) commands for modifying table data. INSERT adds new rows using single-row syntax, INSERT ALL for multi-table inserts, or INSERT from SELECT for bulk operations. UPDATE modifies existing rows with SET clauses that support single-column, multi-column, and subquery-based updates. DELETE removes rows based on WHERE conditions. Because Oracle implicitly starts a transaction after every DML statement, you must issue COMMIT to save changes or ROLLBACK to undo them &#8211; changes are not visible to other sessions until committed.<\/strong><\/p>\n<h2>Introduction<\/h2>\n<p>In this article, we&#8217;ll be focusing on three of the most used SQL commands in Oracle: <code>INSERT, UPDATE<\/code>, and <code>DELETE<\/code>. These commands are used to insert new data into tables, update existing data, and delete data from tables, respectively.<\/p>\n<p>In this article I will take a closer look at each of these statements and how they are used in Oracle. Please note the <code>INSERT, UPDATE<\/code>, and <code>DELETE<\/code> statements can only be used on tables that you have been granted the privilege to execute these commands.<\/p>\n<p>Note that by default, Oracle implicitly starts a transaction after every statement. Because of this, you must use the <code>COMMIT<\/code> or <code>ROLLBACK<\/code> 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 <code>WHERE<\/code> clause that does not return <code>FALSE<\/code> for any rows.) For more detail on Oracle transactions, see <a href=\"https:\/\/docs.oracle.com\/cd\/E25054_01\/server.1111\/e25789\/transact.htm\">Transactions<\/a> in the Oracle documentation)<\/p>\n<p>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:<\/p>\n<ul>\n<li><strong>COMMIT:<\/strong> This statement is used to permanently save the changes made during a transaction. When you issue a <code>COMMIT<\/code> statement, all the changes made during the transaction are made permanent and cannot be undone. After the <code>COMMIT<\/code> statement is executed, a new transaction begins.<\/li>\n<li><strong>ROLLBACK:<\/strong> This statement is used to undo the changes made during a transaction. When you issue a <code>ROLLBACK<\/code> statement, all the changes made during the transaction are undone, and the database is restored to its previous state. After the <code>ROLLBACK<\/code> statement is executed, a new transaction begins.<\/li>\n<\/ul>\n<p>It&#8217;s important to use <code>COMMIT<\/code> and <code>ROLLBACK<\/code> 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.<\/p>\n<p>Here\u2019s an example of an <code>\"Employee\"<\/code> table: You can use the following SQL script to create the <code>\"Employee\"<\/code> table. We will use this table to learn to insert, update and delete commands.<\/p>\n<p>Note: In our following <code>CREATE TABLE<\/code> Statement, we utilized the Oracle <code>NOT NULL<\/code> constraint for <code>Name<\/code>, <code>LastName<\/code>, and <code>Dob<\/code> columns, which ensures that a column cannot store <code>NULL <\/code>values. These constraints are inline and commonly included in the column definition of a <code>CREATE TABLE<\/code> statement. When you define one or more columns as the primary key for a table, it automatically includes the <code>NOT NULL<\/code> constraint, which prohibits <code>NULL<\/code> values from being inserted into that column. If you try to insert a <code>NULL<\/code> value into a primary key column, or one defined as <code>NOT NULL<\/code>, Oracle SQL will raise an error.<\/p>\n<p>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 <code>DEFAULT<\/code> keyword. we have defined the <code>DEFAULT<\/code> value <code>'Unknown'<\/code> for the <code>\"Department\"<\/code> column.<\/p>\n<p>SQL script to create the <code>\"Employee\"<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE Employee (\n    EmployeeId INT PRIMARY KEY,\n    Name VARCHAR(50) NOT NULL,\n    LastName VARCHAR(50) NOT NULL,\n    FirstName VARCHAR(50) NOT NULL,\n    Department VARCHAR(50) DEFAULT 'Unknown',\n    Dob DATE NOT NULL,\n    Salary DECIMAL(10, 2) NOT NULL \n);<\/pre>\n<h2>The INSERT Statement<\/h2>\n<p>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.<\/p>\n<h3>INSERT Statement Basic Syntax:<\/h3>\n<p>The SQL <code>INSERT<\/code> statement is used to add new data to a database. There are different types of SQL <code>INSERT<\/code> statements, each with its syntax and usage. The basic <code>INSERT<\/code> 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.<\/p>\n<p>The basic <code>INSERT<\/code> command is used to add new data to a table in Oracle. The syntax for this command is as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO table_name (column1, column2, ..., columnN)\nVALUES (value1, value2, ..., valueN);<\/pre>\n<p>In this syntax, <code>table_name <\/code>is the name of the table you want to insert data into, and <code>column1<\/code> through <code>columnN<\/code> are the names of the columns you want to insert data into. The <code>VALUES<\/code> keyword is followed by a comma-separated list of values that correspond to the columns you specified earlier.<\/p>\n<p>In this section, we will cover the following different ways of writing SQL <code>INSERT<\/code> statements.<\/p>\n<ul>\n<li>Single row <code>INSERT<\/code> statement.<\/li>\n<li><code>INSERT<\/code> <code>ALL<\/code> statements.<\/li>\n<li><code>INSERT<\/code> from <code>SELECT<\/code> statement.<\/li>\n<\/ul>\n<h3>Single row INSERT statement<\/h3>\n<p>A single-row <code>INSERT<\/code> statement inserts a single row into a table. Let&#8217;s consider an example to understand this statement better. Using the table named <code>\"Employee\"<\/code> as defined at the beginning of the article, to insert a new employee row into this table, you can use the following <code>INSERT<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO Employee (EmployeeId, Name, LastName, FirstName,\n      Department, Dob, Salary)\nVALUES ('1000', 'Allen Cox', 'Cox', 'Allen', 'IT', \n      TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000);\nCOMMIT;<\/pre>\n<p>This statement will add a new row to the <code>\"Employee\"<\/code> table with the specified values.<\/p>\n<p>Now let&#8217;s see what happens if we don&#8217;t specify the <code>\"Department\"<\/code> column in our <code>INSERT<\/code> statement because we already defined a default value for the <code>\"Department\"<\/code> column in the <code>CREATE<\/code> <code>TABLE<\/code> statement.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO Employee (EmployeeId, Name, LastName, FirstName,\n      Dob, Salary)\nVALUES ('1001', 'Allen Cox', 'Cox', 'Allen',  \n     TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000); \n\nCOMMIT;<\/pre>\n<p>You will notice that the <code>\"Department\"<\/code> column has the default value <code>'Unknown'<\/code>, which is automatically assigned because we omitted the column from the <code>INSERT<\/code> statement.<\/p>\n<p><strong>Output:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"536\" height=\"46\" class=\"wp-image-96836\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/c-users-crawat-appdata-local-temp-snaghtml6bcd14e.png\" alt=\"C:\\Users\\CRAWAT\\AppData\\Local\\Temp\\SNAGHTML6bcd14e7.PNG\" \/><\/p>\n<p>Note: It is not required to specify the columns in the <code>INSERT<\/code> statement. So, the following is allowed:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO Employee \nVALUES ('1001', 'Allen Cox', 'Cox', 'Allen',  \n     TO_DATE('01-July-1982', 'DD-MM-YYYY'),75000); \n\nCOMMIT;<\/pre>\n<p>This will be covered later in the article in more detail, but you should always specify the columns for the <code>INSERT<\/code> 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.<\/p>\n<h3>Inserting Multiple Rows at a time<\/h3>\n<p>In this section, I will cover the multiple ways that you can insert more than a single row at a time.<\/p>\n<h4>Multiple Batches<\/h4>\n<p>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.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">INSERT INTO Employee (EmployeeId, Name, LastName, FirstName, \n                      Department, Dob, Salary)\nVALUES ('1002', 'Dave Carry', 'Carry',\t'Dave',\t'Sales', \n       TO_DATE('01-Jan-1992', 'DD-MM-YYYY'),70000);\n\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, \n                      Department, Dob, Salary)\nVALUES ('1003', 'Amit Singh','Singh',\t'Amit',\t'Sales', \n       TO_DATE('20-Oct-1991', 'DD-MM-YYYY'),50000);\n\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, \n                      Department, Dob, Salary) \nVALUES ('1004', 'Rhonda Grant', 'Grant',\t'Rhonda', \n       'Marketing', \n       TO_DATE('15-May-1980', 'DD-MM-YYYY'),60000);\n\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, \n                      Department, Dob, Salary) \nVALUES ('1005', 'Marvin Cox', 'Cox',\t'Marvin', 'Marketing', \n       TO_DATE('15-Jan-1985', 'DD-MM-YYYY'),62000);\n\nINSERT INTO Employee (EmployeeId, Name, LastName, FirstName, \n                      Department, Dob, Salary) \nVALUES ('1006', 'Dave Grant', 'Grant',\t'Dave', 'Sales', \n        TO_DATE('05-Oct-1980', 'DD-MM-YYYY'),90000);\n\nCOMMIT;<\/pre>\n<p>Rerun the <code>SELECT<\/code> statement using the <code>\"Employee\"<\/code> table now looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"615\" height=\"162\" class=\"wp-image-96837\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/graphical-user-interface-table-description-autom.png\" alt=\"Graphical user interface, table\n\nDescription automatically generated\" \/><\/p>\n<p>If there are errors in any of the rows, you can <code>ROLLBACK<\/code> the transaction and none of the new rows would be created.<br \/><strong>Read also: <\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/convert-single-instance-to-rac-part-2-manually-convert-to-rac\/\">Converting a single instance to Oracle RAC<\/a><\/p>\n<h4>INSERT ALL statement<\/h4>\n<p>The <code>INSERT ALL<\/code> 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.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT ALL\n    INTO table1 (column1, column2, ...) \n    VALUES (value1, value2, ...)\n\n    INTO table2 (column1, column2, ...) \n    VALUES (value1, value2, ...)\n\n    INTO table3 (column1, column2, ...) \n    VALUES (value1, value2, ...)\n\nSubquery;<\/pre>\n<p>In the original statement, it is required that each value expression <code>value1<\/code>, <code>value2<\/code>, or <code>value3<\/code> 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 <code>INTO<\/code> and <code>VALUES<\/code> clause. Also, the number of columns and their data types must be compatible between the <code>INTO<\/code> clause and the corresponding <code>VALUES<\/code> clause.<\/p>\n<p>However, to use literal values instead of values returned by the subquery, you can utilize the following subquery:<\/p>\n<p><code>SELECT * FROM dual;<\/code><\/p>\n<p>Here&#8217;s an example:<\/p>\n<p>Suppose we have two tables: <code>\"Employee\"<\/code> and <code>\"Address\"<\/code>. The <code>\"Employee\"<\/code> table is the same as the one we created at the beginning of the article. The <code>\"Address\"<\/code> table is as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE Address (\n   EmployeeId NUMBER PRIMARY KEY,\n   Address_Line1 VARCHAR2(100),\n   Address_Line2 VARCHAR2(100),\n   City VARCHAR2(50),\n   State VARCHAR2(50),\n   Country VARCHAR2(50)\n);<\/pre>\n<p>Let&#8217;s say we want to insert data into both tables using a single <code>INSERT ALL<\/code> statement. We can do this as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT ALL\n\n  INTO Employee (EmployeeId, Name, LastName, FirstName,\n              Department, Dob, Salary)\n  VALUES ('1007', 'Ram Singh', 'Singh', 'Ram', 'IT', \n          TO_DATE('08-July-1982', 'DD-MM-YYYY'),75000)\n \n  INTO Address (EmployeeId, Address_Line1, City, \n                State, Country) \n  VALUES ('1007', '123 Main St', 'Anytown', 'CA', 'USA')\n\n  SELECT * FROM dual; \n\nCOMMIT;<\/pre>\n<p>In this example, we are inserting data into two tables, <code>\"Employee\"<\/code> and <code>\"Address\"<\/code>. The <code>SELECT * FROM dual <\/code>statement at the end is required because the <code>INSERT ALL<\/code> statement requires a subquery (in this case a <code>SELECT<\/code> statement) at the end.<\/p>\n<p>The first <code>INTO<\/code> clause specifies that we want to insert data into the <code>\"Employee\"<\/code> table, and the second <code>INTO<\/code> clause specifies that we want to insert data into the <code>\"Address\"<\/code> table. The <code>SELECT<\/code> statement at the end specifies the data we want to insert.<\/p>\n<p>After executing this statement, the <code>\"Employee\"<\/code> table would contain the following row: <img loading=\"lazy\" decoding=\"async\" width=\"559\" height=\"42\" class=\"wp-image-96838\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96835-3.png\" \/><\/p>\n<p>And the <code>\"Address\"<\/code> table would contain the following row:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"483\" height=\"56\" class=\"wp-image-96839\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/graphical-user-interface-description-automaticall.png\" alt=\"Graphical user interface\n\nDescription automatically generated with medium confidence\" \/><\/p>\n<h4>INSERT ALL subquery example<\/h4>\n<p>Suppose we have two tables, <code>\"Name\"<\/code> and <code>\"Salary\" <\/code>and we want to insert records into both tables using a single <code>INSERT<\/code> <code>ALL<\/code> statement. We&#8217;ll <code>INSERT<\/code> the employee&#8217;s salary and name details from the <code>\"Employee\" <\/code>table.<code> <\/code><\/p>\n<p>You can create <code>\"Name\"<\/code> and <code>\"Salary\" <\/code>table using the following code:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE Name (\n  EmployeeId NUMBER PRIMARY KEY,\n  LastName VARCHAR(50) NOT NULL,\n  FirstName VARCHAR(50) NOT NULL,\n  Dob DATE NOT NULL\n);\n\nCREATE TABLE Salary (\n  EmployeeId NUMBER PRIMARY KEY,\n  Salary DECIMAL (10, 2) NOT NULL \n); <\/pre>\n<p>Let&#8217;s now write an <code>INSERT<\/code> statement to insert the data.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">INSERT ALL\n\n    INTO Name   (EmployeeId, LastName , FirstName , Dob)\n         VALUES (EmployeeId, LastName , FirstName , Dob)\n\n    INTO Salary (EmployeeId , Salary)\n         VALUES (EmployeeId , Salary)\n\nSELECT EmployeeId, LastName , FirstName , Dob, Salary\nFROM Employee ;<\/pre>\n<p>The data in the &#8220;<code>Name<\/code>&#8221; table appears as shown after executing the <code>INSERT ALL<\/code> statement mentioned above.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"334\" height=\"180\" class=\"wp-image-96840\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/a-screenshot-of-a-table-description-automatically.png\" alt=\"A screenshot of a table\n\nDescription automatically generated with low confidence\" \/><\/p>\n<p>Output of the <code>\"Salary\" Table:<\/code><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"165\" height=\"187\" class=\"wp-image-96841\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/a-screenshot-of-a-table-description-automatically-1.png\" alt=\"A screenshot of a table\n\nDescription automatically generated with low confidence\" \/><\/p>\n<h4>INSERT from SELECT statement<\/h4>\n<p>The <code>INSERT INTO SELECT<\/code> 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.<\/p>\n<p>The syntax for the <code>INSERT INTO SELECT<\/code> statement is as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO table_name (column1, column2, ..., columnN) \nSELECT column1, column2, ..., columnN \nFROM source_table \nWHERE condition;<\/pre>\n<p>Just like for the single-row usage of the <code>INSERT<\/code> statement, <code>table_name<\/code> is the name of the table you want to insert data into, and <code>column1<\/code> through <code>columnN<\/code> are the names of the columns you want to insert data into.<\/p>\n<p>The <code>SELECT<\/code> keyword is followed by a list of columns from the source table, and the <code>FROM<\/code> keyword is followed by the name of the source table. The <code>WHERE<\/code> keyword is optional and allows you to filter the data before inserting it into the new table.<\/p>\n<p>For example, the following SQL statement would copy all rows from the <code>\"Employee\"<\/code> table where the <code>\"Department\"<\/code> column is <code>\"Sales\"<\/code> into a new table called <code>\"Sales_Employee\"<\/code>.<\/p>\n<p>Use the following SQL to create the <code>\"Sales_Employee\"<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE Sales_Employee (\n    EmployeeId  varchar(15) Primary key,\n    Name varchar(15),\n    Dob DATE,\n    Salary NUMBER(8,0)\n);<\/pre>\n<p>Use the following SQL to insert the data into the <code>\"Sales_Employee\"<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO Sales_Employee(EmployeeId, Name, Dob, Salary) \nSELECT EmployeeId, Name, Dob, Salary \nFROM Employee\nWHERE Department = 'Sales';\n\nCOMMIT;<\/pre>\n<p>The SQL statement above will put three records into the <code>\"Sales_Employee\"<\/code> table. The output of the <code>SELECT<\/code> statement from the <code>\"Sales_Employee\"<\/code> table looks like this.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"348\" height=\"95\" class=\"wp-image-96842\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/graphical-user-interface-text-application-table.png\" alt=\"Graphical user interface, text, application, table\n\nDescription automatically generated\" \/><\/p>\n<h3>Insert Data Without Using an Explicit Column List<\/h3>\n<p>This brings us to a discussion of <code>INSERT<\/code> statements that do not specify a column list. It is a useful technique when doing quick, ad-hoc work, but it has limitations.<\/p>\n<p>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 <code>NULL<\/code> 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.<\/p>\n<p>In this case, the <code>INSERT INTO<\/code> syntax would be as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO table_name\nVALUES (value1, value2, value3, ...);\n\nCOMMIT;<\/pre>\n<p>So for example, you could write the following statement to add rows from &#8220;Employee&#8221; into the &#8220;Sales_Employee&#8221; table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO Sales_Employee \nSELECT EmployeeId, Name, Dob, Salary From Employee\nWHERE Department = 'Sales';\n\nCOMMIT;<\/pre>\n<p>This is a valid way to write an <code>INSERT<\/code> 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.<\/p>\n<p>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.<\/p>\n<p>There are several reasons why it is considered good practice to specify the column list:<\/p>\n<ul>\n<li><strong>Clarity<\/strong>: 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.<\/li>\n<li><strong>Maintenance<\/strong>: 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.<\/li>\n<li><strong>Performance<\/strong>: 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.<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>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.<br \/><strong>Read also:\u00a0<br \/><\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/exporting-data-from-a-mysql-database-using-selectinto-outfile\/\"><span data-sheets-root=\"1\">Oracle System Change Number explained<br \/>Exporting data from MySQL using SELECT INTO OUTFILE<\/span><\/a><\/p>\n<h2>UPDATE Statement:<\/h2>\n<p>The <code>UPDATE<\/code> 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.<\/p>\n<h3>UPDATE Syntax:<\/h3>\n<p>The basic syntax of the <code>UPDATE<\/code> statement is as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">UPDATE table_name \nSET column1 = <strong>scalar_<\/strong>expression1, \n    column2 = <strong>scalar_<\/strong>expression2,\n     ..., \n    columnN = <strong>scalar_<\/strong>expression2 \nWHERE condition;<\/pre>\n<p>In this syntax:<\/p>\n<ul>\n<li><code>table_name<\/code> is the name of the table you want to update.<\/li>\n<li><code>column_name<\/code> is the name of the column you want to update.<\/li>\n<li><code>scalar_expression<\/code> 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.<\/li>\n<li><code>condition<\/code> is an optional condition that specifies which rows to update. If omitted, all rows in the table will be updated. Let&#8217;s consider an example to understand this statement better.<\/li>\n<\/ul>\n<p><strong>Examples:<\/strong><\/p>\n<p>Suppose you want to update the salary of the employee with an &#8220;<code>EmployeeId\"<\/code> of 1002 to 80000.<\/p>\n<p>Before executing the <code>UPDATE<\/code> statement, the data in the <code>\"Employee\"<\/code> table looks like the following:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"572\" height=\"52\" class=\"wp-image-96843\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/a-picture-containing-text-description-automatical.png\" alt=\"A picture containing text\n\nDescription automatically generated\" \/><\/p>\n<p>You can use the following <code>UPDATE<\/code> statement.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">UPDATE Employee \nSET Salary = 80000 \nWHERE EmployeeId = 1002;\n\nCOMMIT;<\/pre>\n<p>After executing the <code>UPDATE<\/code> statement, the data in the <code>\"Employee\"<\/code> table will look like the following as we can see the Salary column is updated to 80000.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"570\" height=\"48\" class=\"wp-image-96844\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96835-9.png\" \/><\/p>\n<p>As an example of updating multiple columns, let&#8217;s edit &#8220;<code>Salary\"<\/code>, &#8220;<code>Department\"<\/code>, and &#8220;<code>LastName\"<\/code> column values using a single <code>UPDATE<\/code> statement. Before executing the <code>UPDATE<\/code> statement, the data in the <code>\"Employee\"<\/code> table looks like the following:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"570\" height=\"48\" class=\"wp-image-96845\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/word-image-96835-10.png\" \/><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">UPDATE Employee \nSET Salary = 70000, \n    Department='Sales',\n    LastName='Brown' \nWHERE EmployeeId = 1002;\n\nCOMMIT;<\/pre>\n<p>After running the <code>UPDATE<\/code> statement, you would notice that the &#8220;<code>Salary\"<\/code>, &#8220;<code>Department\"<\/code>, and &#8220;<code>LastName\"<\/code> column values for <code>\"EmployeeId\"<\/code> 1002 have been updated to this.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"562\" height=\"56\" class=\"wp-image-96846\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/a-picture-containing-timeline-description-automat.png\" alt=\"A picture containing timeline\n\nDescription automatically generated\" \/><\/p>\n<p>Now let\u2019s use an arithmetic expression to <code>update<\/code> the <code>\"Salary\"<\/code> column in the <code>\"Employee\"<\/code> table:<\/p>\n<p>Before executing the <code>UPDATE<\/code> statement, the data in the <code>\"Employee\"<\/code> table looks like the following:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"566\" height=\"84\" class=\"wp-image-96847\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/table-description-automatically-generated.png\" alt=\"Table\n\nDescription automatically generated\" \/><\/p>\n<p>Now run the following <code>UPDATE<\/code> statement:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">UPDATE Employee\nSET Salary = Salary * 1.1\nWHERE department = 'Sales';\n\nCOMMIT;<\/pre>\n<p>We are using an arithmetic expression to update the salary column of all employees in the Sales department. The <code>SET<\/code> clause sets the <code>\"Salary\"<\/code> column to the current value of salary multiplied by 1.1, effectively giving all sales employees a 10% raise.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"568\" height=\"79\" class=\"wp-image-96848\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/table-description-automatically-generated-with-me.png\" alt=\"Table\n\nDescription automatically generated with medium confidence\" \/><\/p>\n<h3>Update using a subquery:<\/h3>\n<p>The following statement will update the <code>\"Salary\"<\/code> column of all rows in the <code>\"Employee\"<\/code> table where the <code>\"Department\"<\/code> column equals <code>'Sales'<\/code> to the average salary of employees in the Sales department.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">UPDATE Employee\nSET Salary = (\n  SELECT AVG(Salary)\n  FROM Employees\n  WHERE Department = 'Sales'\n)\nWHERE Department = 'Sales';\n\nCOMMIT;<\/pre>\n<p>After running the <code>UPDATE<\/code> statement, you would notice the following change in the Salary Column of table &#8220;Employee&#8221;.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"598\" height=\"161\" class=\"wp-image-96849\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/05\/graphical-user-interface-application-table-desc.png\" alt=\"Graphical user interface, application, table\n\nDescription automatically generated\" \/><\/p>\n<h4>Tips for Using the UPDATE Statement<\/h4>\n<ol>\n<li><strong>Pay attention to the WHERE clause: <\/strong>Use the <code>WHERE<\/code> 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 <code>WHERE<\/code> clause, you risk updating all rows in the table.<\/li>\n<li><strong>Keep backups of your data: <\/strong>Be careful when using the <code>UPDATE<\/code> 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.<\/li>\n<li><strong>Use subqueries carefully: <\/strong>Subqueries can be a powerful tool when working with the <code>UPDATE<\/code> 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.<\/li>\n<li><strong>Test your UPDATE statement first: <\/strong>Before running an <code>UPDATE<\/code> 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.<br \/><strong><br \/>Read also<\/strong>:<a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/subqueries-in-sql-server\/\">T-SQL equivalents for INSERT and UPDATE<\/a><\/li>\n<\/ol>\n<h2>DELETE Statement:<\/h2>\n<p>The <code>DELETE<\/code> statement is used to remove one or more rows from a table. The <code>DELETE<\/code> statement is utilized for managing data in a database, and it is essential to understand how to use it effectively.<\/p>\n<h3>DELETE Basic Syntax:<\/h3>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">DELETE FROM table_name \nWHERE condition;<\/pre>\n<p>In this syntax, &#8216;table_name&#8217; is the name of the table from which you want to delete rows, and &#8216;condition&#8217; 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.<\/p>\n<p>For example, the following SQL statement would delete all rows from the &#8220;Employee&#8221; table where the &#8220;Department&#8221; column is &#8216;Sales&#8217;.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DELETE FROM Employee \nWHERE Department = 'Sales';\n\nCOMMIT;<\/pre>\n<p>If you want to delete all rows from a table, you can omit the <code>WHERE<\/code> clause:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DELETE FROM table_name;<\/pre>\n<p>This statement will delete all rows from the specified table.<\/p>\n<p>For example, the following SQL statement would delete all rows from the &#8220;Employee&#8221; table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DELETE FROM Employee;\n\nCOMMIT;<\/pre>\n<h3>Deleting Data using a subquery<\/h3>\n<p>In some cases, you may need to delete rows based on values in another table. Oracle allows you to do this using the <code>DELETE<\/code> statement with a subquery. The subquery is used to specify which rows to delete from the target table.<\/p>\n<p>The syntax for deleting data from multiple tables using a subquery is as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DELETE FROM table_name1 \nWHERE column_name IN (SELECT column_name \n                      FROM table_name2 \n                      WHERE condition);<\/pre>\n<p>In this syntax, <code>table_name1<\/code> is the target table, and <code>table_name2<\/code> 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.<\/p>\n<p>For example, the following SQL statement would delete all employees from the &#8220;Employee&#8221; table whose Salary is greater than 60000 in the <code>\"Sales_Employee\"<\/code> table.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">DELETE FROM Employee \nWHERE Employeeid IN (SELECT Employeeid \n                     FROM Sales_Employee \n                     WHERE SALARY &gt; 60000);\n\nCOMMIT;<\/pre>\n<h3>Tips for Using the DELETE Statement<\/h3>\n<ol>\n<li>Pay attention to the <code>WHERE<\/code> clause to specify the conditions for deleting rows. If you omit the <code>WHERE<\/code> clause, you risk deleting all rows in the table. (Which is quite bad if not what you expected to do.)<\/li>\n<li>It&#8217;s important to exercise caution when using the <code>DELETE<\/code> 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.<\/li>\n<li>Use the <code>TRUNCATE<\/code> statement instead of <code>DELETE<\/code> when you need to delete all rows from a table. <code>TRUNCATE<\/code> is much faster than <code>DELETE<\/code> for this purpose. (Note that <code>TRUNCATE<\/code> has limitations and security differences to <code>DELETE<\/code>, for more detail on <code>TRUNCATE<\/code>, check out the <a href=\"https:\/\/docs.oracle.com\/database\/121\/SQLRF\/statements_10007.htm#SQLRF01707\">Oracle documentation here<\/a>.)<\/li>\n<\/ol>\n<h2>Summary<\/h2>\n<p>In this article, we have looked at the usage of SQL commands, <code>INSERT, UPDATE,<\/code> and <code>DELETE<\/code> in Oracle, one of the most widely used relational database management systems. The article focuses on the basic syntax and types of SQL <code>INSERT<\/code> statements in Oracle, including single row and inserts from <code>SELECT<\/code> statements with examples.<\/p>\n<p>Additionally, it covers the basic syntax of <code>UPDATE<\/code> and <code>DELETE<\/code> 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.<\/p>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: INSERT, UPDATE and DELETE statements in Oracle<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you insert multiple rows in Oracle?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Oracle supports several approaches for multi-row inserts. Use INSERT ALL to insert into one or more tables from a single statement, specifying each INTO clause with its target table and values. For bulk inserts from existing data, use INSERT INTO &#8230; SELECT to copy rows from one table to another based on a query. Oracle does not support the multi-row VALUES syntax used in SQL Server (INSERT INTO table VALUES (row1), (row2)), so INSERT ALL or INSERT from SELECT are the standard approaches.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What is the difference between DELETE and TRUNCATE in Oracle?<\/h3>\n            <div class=\"faq-answer\">\n                <p>DELETE removes specific rows based on a WHERE condition and generates redo\/undo data, meaning the operation can be rolled back with ROLLBACK. TRUNCATE removes all rows from a table instantly without generating row-level undo &#8211; it\u2019s a DDL command that auto-commits and cannot be rolled back. Use DELETE when you need to remove specific rows or need the ability to undo the operation. Use TRUNCATE when you need to empty an entire table quickly.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. How do you update multiple columns in a single Oracle UPDATE statement?<\/h3>\n            <div class=\"faq-answer\">\n                <p>In the SET clause of an UPDATE statement, list each column assignment separated by commas: UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3 WHERE condition. You can also use a subquery to update multiple columns from another table: UPDATE table_name SET (column1, column2) = (SELECT col_a, col_b FROM other_table WHERE &#8230;) WHERE condition.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Why are my Oracle INSERT or UPDATE changes not visible to other users?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Oracle uses an implicit transaction model &#8211; every DML statement starts a transaction automatically. Your changes are only visible to your own session until you issue a COMMIT statement. Other users will see the data as it existed before your transaction began. This is Oracle\u2019s read consistency mechanism, powered by the System Change Number (SCN). If you need to undo your changes, use ROLLBACK before committing.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Complete reference for Oracle DML statements. Covers INSERT (single-row and multi-row), UPDATE with conditions, DELETE vs TRUNCATE \u2014 with tested code<br \/>\nexamples for each.&hellip;<\/p>\n","protected":false},"author":341091,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143533],"tags":[],"coauthors":[158987],"class_list":["post-96835","post","type-post","status-publish","format-standard","hentry","category-featured","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341091"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96835"}],"version-history":[{"count":15,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96835\/revisions"}],"predecessor-version":[{"id":110745,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96835\/revisions\/110745"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96835"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}