ORACLE SQL INSERT

SQL INSERT STATEMENT

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.

 

There are two ways to insert data in a table:

  1. By SQL insert into statement
    1. By specifying column names
    2. Without specifying column names
  2. By SQL insert into select statement

1) Inserting data directly into a table

You can insert a row in the table by using SQL INSERT INTO command.

There are two ways to insert values in a table.

In the first method there is no need to specify the column name where the data will be inserted, you need only their values.

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

The second method specifies both the column name and values which you want to insert.

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

Let's take an example of table which has five records within it.

  1. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
  2. VALUES (1, ABHIRAM, 22, ALLAHABAD);
  3. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
  4. VALUES (2, ALKA, 20, GHAZIABAD);
  5. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
  6. VALUES (3, DISHA, 21, VARANASI);
  7. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
  8. VALUES (4, ESHA, 21, DELHI);
  9. INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)
  10. VALUES (5, MANMEET, 23, JALANDHAR);

It will show the following table as the final result.

ROLL_NO NAME AGE CITY
1 ABHIRAM 22 ALLAHABAD
2 ALKA 20 GHAZIABAD
3 DISHA 21 VARANASI
4 ESHA 21 DELHI
5 MANMEET 23 JALANDHAR

You can create a record in CUSTOMERS table by using this syntax also.

  1. INSERT INTO CUSTOMERS
  2. VALUES (6, PRATIK, 24, KANPUR);

The following table will be as follow:

ROLL_NO NAME AGE CITY
1 ABHIRAM 22 ALLAHABAD
2 ALKA 20 GHAZIABAD
3 DISHA 21 VARANASI
4 ESHA 21 DELHI
5 MANMEET 23 JALANDHAR
6 PRATIK 24 KANPUR

2) Inserting data through SELECT Statement

SQL INSERT INTO SELECT Syntax

  1. INSERT INTO table_name
  2. [(column1, column2, .... column)]
  3. SELECT column1, column2, .... Column N
  4. FROM table_name [WHERE condition];

Note: when you add a new row, you should make sure that data type of the value and the column should be matched.

If any integrity constraints are defined for the table, you must follow them.

 

 

SQL INSERT Multiple Rows

Many times developers ask that is it possible to insert multiple rows into a single table in a single statement. Currently, developers have to write multiple insert statements when they insert values in a table. It is not only boring but also time-consuming.

Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

Example 1:

To create a table in the database, first, we need to select the database in which we want to create a table.

  1. mysql> USE dbs;

Then we will write a query to create a table named student in the selected database 'dbs'.

  1. mysql> CREATE TABLE student(ID INT, Name VARCHAR(20), Percentage INT, Location VARCHAR(20), DateOfBirth DATE);

SQL INSERT Multiple Rows

The student table is created successfully.

Now, we will write a single query to insert multiple records in the student table:

  1. mysql> INSERT INTO student(ID, Name, Percentage, Location, DateOfBirth) VALUES(1, "Manthan Koli", 79, "Delhi", "2003-08-20"), (2, "Dev Dixit", 75, "Pune", "1999-06-17"), (3, "Aakash Deshmukh", 87, "Mumbai", "1997-09-12"), (4, "Aaryan Jaiswal", 90, "Chennai", "2005-10-02"), (5, "Rahul Khanna", 92, "Ambala", "1996-03-04"), (6, "Pankaj Deshmukh", 67, "Kanpur", "2000-02-02"), (7, "Gaurav Kumar", 84, "Chandigarh", "1998-07-06"), (8, "Sanket Jain", 61, "Shimla", "1990-09-08"), (9, "Sahil Wagh", 90, "Kolkata", "1968-04-03"), (10, "Saurabh Singh", 54, "Kashmir", "1989-01-06");

SQL INSERT Multiple Rows

To verify that multiple records are inserted in the student table, we will execute the SELECT query.

  1. mysql> SELECT *FROM student;

 

ID Name Percentage Location DateOfBirth
1 Manthan Koli 79 Delhi 2003-08-20
2 Dev Dixit 75 Pune 1999-06-17
3 Aakash Deshmukh 87 Mumbai 1997-09-12
4 Aaryan Jaiswal 90 Chennai 2005-10-02
5 Rahul Khanna 92 Ambala 1996-03-04
6 Pankaj Deshmukh 67 Kanpur 2000-02-02
7 Gaurav Kumar 84 Chandigarh 1998-07-06
8 Sanket Jain 61 Shimla 1990-09-08
9 Sahil Wagh 90 Kolkata 1968-04-03
10 Saurabh Singh 54 Kashmir 1989-01-06

The results show that all ten records are inserted successfully using a single query.

Example 2:

To create a table in the database, first, we need to select the database in which we want to create a table.

  1. mysql> USE dbs;

Then we will write a query to create a table named items_tbl in the selected database 'dbs'.

  1. mysql> CREATE TABLE items_tbl(ID INT, Item_Name VARCHAR(20), Item_Quantity INT, Item_Price INT, Purchase_Date DATE);

SQL INSERT Multiple Rows

The table named items_tbl is created successfully.

Now, we will write a single query to insert multiple records in the items_tbl table:

  1. mysql> INSERT INTO items_tbl(ID, Item_Name, Item_Quantity, Item_Price, Purchase_Date) VALUES(1, "Soap", 5, 200, "2021-07-08"), (2, "Toothpaste", 2, 80, "2021-07-10"), (3, "Pen", 10, 50, "2021-07-12"), (4, "Bottle", 1, 250, "2021-07-13"), (5, "Brush", 3, 90, "2021-07-15"), (6, "Notebooks", 10, 1000, "2021-07-26"), (7, "Handkerchief", 3, 100, "2021-07-28"), (8, "Chips Packet", 5, 50, "2021-07-30"), (9, "Marker", 2, 30, "2021-08-13"), (10, "Scissors", 1, 60, "2021-08-13");

SQL INSERT Multiple Rows

To verify that multiple records are inserted in the items_tbl table, we will execute the SELECT query.

  1. mysql> SELECT *FROM items_tbl;

 

ID Item_Name Item_Quantity Item_Price Purchase_Date
1 Soap 5 200 2021-07-08
2 Toothpaste 2 80 2021-07-10
3 Pen 10 50 2021-07-12
4 Bottle 1 250 2021-07-13
5 Brush 3 90 2021-07-15
6 Notebooks 10 1000 2021-07-26
7 Handkerchief 3 100 2021-07-28
8 Chips Packet 5 50 2021-07-30
9 Marker 2 30 2021-08-13
10 Scissors 1 60 2021-08-13

The results show that all ten records are inserted successfully using a single query.

 

 

insert into label_request values (123445,'srihari.boda@oracle.com','sboda,fekhan');   # Use single quotes but not double quotes

 

SQL UPDATE

The SQL commands (UPDATE and DELETE) are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause.

SQL UPDATE statement is used to change the data of the records held by tables. Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause.

The UPDATE statement can be written in following form:

  1. UPDATE table_name SET [column_name1= value1,... column_nameN = valueN] [WHERE condition]

Let's see the Syntax:

  1. UPDATE table_name
  2. SET column_name = expression
  3. WHERE conditions

Let's take an example: here we are going to update an entry in the source table.

SQL statement:

  1. UPDATE students
  2. SET User_Name = 'beinghuman'
  3. WHERE Student_Id = '3'

Source Table:

Student_Id FirstName LastName User_Name
1 Ada Sharma sharmili
2 Rahul Maurya sofamous
3 James Walker jonny

See the result after updating value:

Student_Id FirstName LastName User_Name
1 Ada Sharma sharmili
2 Rahul Maurya sofamous
3 James Walker beinghuman

Updating Multiple Fields:

If you are going to update multiple fields, you should separate each field assignment with a comma.

SQL UPDATE statement for multiple fields:

  1. UPDATE students
  2. SET User_Name = 'beserious', First_Name = 'Johnny'
  3. WHERE Student_Id = '3'

Result of the table is given below:

Student_Id FirstName LastName User_Name
1 Ada Sharma sharmili
2 Rahul Maurya sofamous
3 Johnny Walker beserious

MYSQL SYNTAX FOR UPDATING TABLE:

  1. UPDATE table_name
  2. SET field1 = new-value1, field2 = new-value2,
  3. [WHERE CLAUSE]

SQL UPDATE SELECT:

SQL UPDATE WITH SELECT QUERY:

We can use SELECT statement to update records through UPDATE statement.

SYNTAX:

  1. UPDATE tableDestination
  2. SET tableDestination.col = value
  3. WHERE EXISTS (
  4. SELECT col2.value
  5. FROM  tblSource
  6. WHERE tblSource.join_col = tblDestination. Join_col
  7. AND  tblSource.Constraint = value)

You can also try this one -

  1. UPDATE
  2. Table
  3. SET
  4. Table.column1 = othertable.column 1,
  5. Table.column2 = othertable.column 2
  6. FROM
  7. Table
  8. INNER JOIN
  9. Other_table
  10. ON
  11. Table.id = other_table.id

My SQL SYNTAX:

If you want to UPDATE with SELECT in My SQL, you can use this syntax:

Let's take an example having two tables. Here,

First table contains -

Cat_id, cat_name,

And the second table contains -

Rel_cat_id, rel_cat_name

SQL UPDATE COLUMN:

We can update a single or multiple columns in SQL with SQL UPDATE query.

SQL UPDATE EXAMPLE WITH UPDATING SINGLE COLUMN:

  1. UPDATE students
  2. SET student_id = 001
  3. WHERE student_name = 'AJEET';

This SQL UPDATE example would update the student_id to '001' in the student table where student_name is 'AJEET'.

SQL UPDATE EXAMPLE WITH UPDATING MULTIPLE COLUMNS:

To update more than one column with a single update statement:

  1. UPDATE students
  2. SET student_name = 'AJEET',
  3. Religion = 'HINDU'
  4. WHERE student_name = 'RAJU';

This SQL UPDATE statement will change the student name to 'AJEET' and religion to 'HINDU' where the student name is 'RAJU'.

 

SQL UPDATE with JOIN

SQL UPDATE JOIN means we will update one table using another table and join condition.

Let us take an example of a customer table. I have updated customer table that contains latest customer details from another source system. I want to update the customer table with latest data. In such case, I will perform join between target table and source table using join on customer ID.

Let's see the syntax of SQL UPDATE query with JOIN statement.

  1. UPDATE customer_table
  2. INNER JOIN
  3. Customer_table
  4. ON customer_table.rel_cust_name = customer_table.cust_id
  5. SET customer_table.rel_cust_name = customer_table.cust_name

How to use multiple tables in SQL UPDATE statement with JOIN

Let's take two tables, table 1 and table 2.

Create table1

  1. CREATE TABLE table1 (column1 INT, column2 INT, column3 VARCHAR (100))
  2. INSERT INTO table1 (col1, col2, col3)
  3. SELECT 1, 11, 'FIRST'
  4. UNION ALL
  5. SELECT 11,12, 'SECOND'
  6. UNION ALL
  7. SELECT 21, 13, 'THIRD'
  8. UNION ALL
  9. SELECT 31, 14, 'FOURTH'

Create table2

  1. CREATE TABLE table2 (column1 INT, column2 INT, column3 VARCHAR (100))
  2. INSERT INTO table2 (col1, col2, col3)
  3. SELECT 1, 21, 'TWO-ONE'
  4. UNION ALL
  5. SELECT 11, 22, 'TWO-TWO'
  6. UNION ALL
  7. SELECT 21, 23, 'TWO-THREE'
  8. UNION ALL
  9. SELECT 31, 24, 'TWO-FOUR'

Now check the content in the table.

  1. SELECT * FROM table_1
  1. SELECT * FROM table_2
Col 1 Col 2 Col 3
1 1 11 First
2 11 12 Second
3 21 13 Third
4 31 14 Fourth

 

Col 1 Col 2 Col 3
1 1 21 Two-One
2 11 22 Two-Two
3 21 23 Two-Three
4 31 24 Two-Four

Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31. We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.

We want to also update the values of Col 2 and Col 3 only.

The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement.

  1. UPDATE table 1
  2. SET Col 2 = t2.Col2,
  3. Col 3 = t2.Col3
  4. FROM table1 t1
  5. INNER JOIN table 2 t2 ON t1.Col1 = t2.col1
  6. WHERE t1.Col1 IN (21,31)

Check the content of the table

SELECT FROM table 1

SELECT FROM table 2

Col 1 Col 2 Col 3
1 1 11 First
2 11 12 Second
3 21 23 Two-Three
4 31 24 Two-Four

 

Col 1 Col 2 Col 3
1 1 21 First
2 11 22 Second
3 21 23 Two-Three
4 31 24 Two-Four

Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.

SQL UPDATE DATE

How to update a date and time field in SQL?

If you want to update a date & time field in SQL, you should use the following query.

let's see the syntax of sql update date.

  1. UPDATE table
  2. SET Column_Name = 'YYYY-MM-DD HH:MM:SS'
  3. WHERE Id = value

Let us check this by an example:

Firstly we take a table in which we want to update date and time fields.

If you want to change the first row which id is 1 then you should write the following syntax:

  1. UPDATE table
  2. SET EndDate = '2014-03-16 00:00:00.000'
  3. WHERE Id = 1

<pNote: you should always remember that SQL must attach default 00:00:00.000 automatically.

 

This query will change the date and time field of the first row in that above assumed table.

 

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

  1. DELETE FROM table_name [WHERE condition];

But if you do not specify the WHERE condition it will remove all the rows from the table.

  1. DELETE FROM table_name;

There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.

Difference between DELETE and TRUNCATE statements

There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.

But it does not free the space containing by the table.

The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

Let's see an "employee" table.

Emp_id Name Address Salary
1 Aryan Allahabad 22000
2 Shurabhi Varanasi 13000
3 Pappu Delhi 24000

Execute the following query to truncate the table:

  1. TRUNCATE TABLE employee;

Difference b/w DROP and TRUNCATE statements

When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.

When you drop a table:

  • Table structure will be dropped
  • Relationship will be dropped
  • Integrity constraints will be dropped
  • Access privileges will also be dropped

On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.

SQL DELETE ROW

Let us take an example of student.

Original table:

ID STUDENT _NAME ADDRESS
001 AJEET MAURYA GHAZIABAD
002 RAJA KHAN LUCKNOW
003 RAVI MALIK DELHI

If you want to delete a student with id 003 from the student_name table, then the SQL DELETE query should be like this:

  1. DELETE FROM student_name
  2. WHERE id = 003;

Resulting table after SQL DELETE query:

ID STUDENT_NAME ADDRESS
001 AJEET MAURYA GHAZIABAD
002 RAJA KHAN LUCKNOW

SQL DELETE ALL ROWS

The statement SQL DELETE ALL ROWS is used to delete all rows from the table. If you want to delete all the rows from student table the query would be like,

  1. DELETE FROM STUDENT_NAME;

Resulting table after using this query:

ID STUDENT_NAME ADDRESS

 

SQL DELETE DUPLICATE ROWS

If you have got a situation that you have multiple duplicate records in a table, so at the time of fetching records from the table you should be more careful. You make sure that you are fetching unique records instead of fetching duplicate records.

To overcome with this problem we use DISTINCT keyword.

It is used along with SELECT statement to eliminate all duplicate records and fetching only unique records.

SYNTAX:

The basic syntax to eliminate duplicate records from a table is:

  1. SELECT DISTINCT column1, column2,....columnN
  2. FROM table _name
  3. WHERE [conditions]

EXAMPLE:

Let us take an example of STUDENT table.

ROLL_NO NAME PERCENTAGE ADDRESS
1 AJEET MAURYA 72.8 ALLAHABAD
2 CHANDAN SHARMA 63.5 MATHURA
3 DIVYA AGRAWAL 72.3 VARANASI
4 RAJAT KUMAR 72.3 DELHI
5 RAVI TYAGI 75.5 HAPUR
6 SONU JAISWAL 71.2 GHAZIABAD

Firstly we should check the SELECT query and see how it returns the duplicate percentage records.

  1. SQL > SELECT PERCENTAGE FROM STUDENTS
  2. ORDER BY PERCENTAGE;
PERCENTAGE
63.5
71.2
72.3
72.3
72.8
75.5

Now let us use SELECT query with DISTINCT keyword and see the result. This will eliminate the duplicate entry.

  1. SQL > SELECT DISTINCT PERCENTAGE FROM STUDENTS
  2. ORDER BY PERCENTAGE;
PERCENTAGE
63.5
71.2
72.3
72.8
75.5

SQL DELETE DATABASE

You can easily remove or delete indexes, tables and databases with the DROP statement.

The DROP index statement is:

Used to delete index in the table

DROP INDEX SYNTAX for MS Access:

  1. DROP INDEX index_name ON table_name

DROP INDEX SYNTAX for MS SQL Server:

  1. DROP INDEX table_name.index_name

DROP INDEX syntax for DB2/Oracle:

  1. DROP INDEX index_name

DROP INDEX syntax for MySQL:

  1. ALTER TABLE table_name DROP INDEX index_name

DROP DATABASE Statement:

The drop database statement is used to delete a database.

  1. DROP DATABASE database_name

Note:

We should always note that in RDBMS, database name should be unique.

We should always remember that DROP database command may be the cause of loss of all information so we should always be careful while doing this operation.

 

SQL DELETE VIEW

Before knowing about what is SQL delete view, it is important to know -

What is SQL view?

A view is a result set of a stored query on the data.

The SQL view is a table which does not physically exist. It is only a virtual table.SQL VIEW can be created by a SQL query by joining one or more table.

Syntax for SQL create view -

  1. CREATE VIEW view_name AS
  2. SELECT columns
  3. FROM tables
  4. WHERE conditions;

If you want to delete a SQL view, It is done by SQL DROP command you should use the following syntax:

SQL DROP VIEW syntax:

  1. DROP VIEW view_name

Why use the SQL DROP VIEW statement?

When a view no longer useful you may drop the view permanently. Also if a view needs change within it, it would be dropped and then created again with changes in appropriate places.

 

SQL DELETE JOIN

This is very commonly asked question that how to delete or update rows using join clause

It is not a very easy process, sometimes, we need to update or delete records on the basis of complex WHERE clauses.

There are three tables which we use to operate on SQL syntax for DELETE JOIN.

These tables are table1, table2 and target table.

SQL Syntax for delete JOIN

  1. DELETE [target table]
  2. FROM    [table1]
  3.         INNER JOIN [table2]
  4. ON [table1.[joining column] = [table2].[joining column]
  5. WHERE   [condition]

Syntax for update

  1. UPDATE [target table]
  2. SET [target column] = [new value]
  3. FROM    [table1]
  4.         INNER JOIN [table2]
  5. ON [table1.[joining column] = [table2].[joining column]
  6. WHERE   [condition]