SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

Commit and Rollback in SQL

In Structured Query Language, we have Data Definition Language (DDL) and Data Manipulation Language (DML) commands the same way we have Transaction Control Language (TCL) commands in the Structured Query Language.

Transaction Control Language commands in the Structured Query Language are Commit and Rollback.

Whatever commands we executed wrapped in one unit of work known as a transaction.

All the operations or commands of DDL or DML are stored or executed in a transaction. Suppose we have executed one update or delete operation on a table executed in a transaction. To save such executed DDL or DML, we have to execute the commit command of a Transaction Control Language. Commit is used to save all the operations we performed on a table, and all the operation is saved. All this is about Commit.

Now, think, what if we want to undo the operations we saved using the commit commands? Then can we undo the operation in the Structured Query Language? Yes, we can undo the committed operations; we will use another command of Transaction Control Language to undo the operations, and that command is Rollback. We will use the Rollback command to undo the commit operation in the Structured Query Language.

Let us see a few examples of Commit and Rollback commands:

Consider the existing employees tables which have the following records:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM

We can use the commit and Rollback command without starting the transactions, but we will start our transaction using Start Transaction Command for good practice.

Let’s begin and see each operation example one by one.

First, we will turn off the auto-commit by assigning a value of auto-commit to 0

SET AUTOCOMMIT = 0;

Example 1:  In this example, we will insert new values into existing table employees and then will commit

INSERT INTO EMPLOYEES VALUES(2001, ‘SURILI’, ‘JAIN’,  55000, ‘MUMBAI’,  ‘TESTING’, ‘F’);

We will use the SELECT query to check the record that is inserted successfully or not in the employee's table:

SELECT * FROM EMPLOYEES;

The output of the above query is as follows:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM
2001SuriliJain55000MumbaiTestingF
Commit and Rollback in SQL

As we can see, the record, which we insert into the table is successfully inserted.

Now, to save this transaction, we will use the commit command.

COMMIT;
Commit and Rollback in SQL

The employee id 2001 details are saved now, and it won't be Rollback until and unless we delete it using the Delete command.

Now, I want to delete the employee whose id is 2001. We will use the Delete operation to delete this employee's details

DELETE FROM EMPLOYEES WHERE EMPLOYEEID = 2001;

We will execute the Select query to verify whether the records we deleted are successfully deleted or not.

SELECT * FROM EMPLOYEES;

The output of the above query is as follows:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM
Commit and Rollback in SQL

We deleted the employee whose id was 2001. Now, what if I want to undo the Delete operation committed, can we undo the delete operation? Yes, we can undo the operation committed using the Rollback command. Rollback is used to undo the transaction.

We will execute the ROLLBACK command to undo the operation.

ROLLBACK;
Commit and Rollback in SQL

We will execute the Select query to view the records of the employee's table. We undo the delete operation. But how will you know whether the Delete operation is Rollback or not?

SELECT * FROM EMPLOYEES;

The output of the above query is as follows:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1001PranotiShende48000KolhapurFMWF
1002VaibhavSharma20000NoidaFMWM
1003NikhilVani55000IndoreOracleM
1004PrachiSharma55000NoidaOracleF
1005HarshadaKoli48500NashikAngularF
1006SonalMaheshwari24000PuneOracleF
1007BhaveshJain65000PuneFMWM
1008KapilVerma50000NashikAngularM
1009RajeshGoud55000IndoreTestingM
1010DeepamJauhari60000IndoreTestingM
2001SuriliJain55000MumbaiTestingF
Commit and Rollback in SQL

The above output clearly says us that we successfully undo the Delete operation using the Rollback command.

Now next operation we will see is on the Update operation. We will write a query to update the employee salary whose employee id is 1002 and set the employee salary as 48500.

UPDATE EMPLOYEES SET EMPLOYEE_SALARY = 48500 WHERE EMPLOYEEID = 1002;

Again, we will execute the Select query to check whether the employee salary is modified or not.

SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = 1002;
EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1002VaibhavSharma48500NoidaFMWM
Commit and Rollback in SQL

The result clearly itself says that employee salary is modified. What if I want to undo the update operation of employee id 1002? Will I need to execute an update operation on employee Id 1002 again? Rather than executing an update operation on employee id 1002 and modifying the salary, we will undo the update operation using Rollback Command.

ROLLBACK;

Commit and Rollback in SQL

We undo the update operation now, will execute the Select query, and check whether it is Rollback or not.

 SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = 1002;

The output of the above query is as follows:

EmployeeidFirst_NameLast_NameEmployee_SalaryEmployee_CityEmployee_DepartmentGender
1002VaibhavSharma20000NoidaFMWM
Commit and Rollback in SQL

As we can see, the employee id 1002 employee salary as same earlier before executing the Update query.

Note: We can undo the Delete command and truncate command, but we cannot undo the Drop command because Drop removes all the data and Drop the table structure and Drop all the index or views related to that table.



ADVERTISEMENT
ADVERTISEMENT