SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast 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 Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

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

Save Point in SQL

In SQL, the classification is done into 4 languages. They are

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Transaction Control Language (TCL)
  4. Data Control Language (DCL)

Save Point falls under the Transaction Control Language. Transaction is a unit of work in the database, which is formed after execution of many commandsconsecutively. So, Transaction Control Language handles/manages all the transactions in a database.

Under Transaction control Language we also have roll back and commit along with the save point command.

Commit:

Once we write all the data into the tables and perform all the necessary operations on the table, we should commit the work for long lasting storage.

When we perform any Data Manipulation Language commands like insert, delete, update on a table in the database, those manipulations will stay alive till the current session of the database terminates.

Once the current session terminates, all the manipulations on the table will be washed off permanently. At this time, COMMIT command helps us to save all the manipulation progress on the tables permanently.

Syntax for commit:

COMMIT;

Example:

Consider a table of Student data having Name, ID, Age as fields.

IDNameAge
511Daksh18
512Ravi17
513Priya19

Now let’s try to insert records into the table.

insert into Student values (514, ‘Yash’, 18);
insert into Student values (515, ‘Keerthi’, 19);
select * from Student;
IDNameAge
511Daksh18
512Ravi17
513Priya19
514Yash18
515Keerthi19

So, the table is updated with two new rows. Now, we should commit the changes in the table.

COMMIT;

So, the two rows are added to the table permanently.

Now, let’s update the age of Yash from 18 to 19.

Update Student set Age = 19 where ID = 514;
select * from Student;
IDNameAge
511Daksh18
512Ravi17
513Priya19
514Yash19
515Keerthi19

Again, after the update command operations on the table, we should again commit the changes for Permanent Storage.

Save Point:

We perform many operations on a particular table. All these operations are classified into two parts. Consider a part of the transactions having all delete statements and another part of the transaction having all update statements. For saving the insert queries into the insert part and delete queries into the delete part, we prefer to use Save point command in SQL.

Syntax:

Let’s suppose that we want to save all the insert related queries into an insert save point. For implementing that we will perform the following query,

SAVEPOINT savepoint name;

Example:

Consider a customer table having Name, ID, and Age as the fields of the table.

IDNameAge
2551Ravi23
2552Virat24
2553Rinku26
2554Sharma18
2555Shravya20

Now let’s delete a row having 2552 as the customer ID.

delete from Customer where ID = 2552;
select * from Customer;
IDNameAge
2551Ravi23
2553Rinku26
2554Sharma18
2555Shravya20

Now we will create a savepoint.

SAVEPOINT sp1;

This will create a savepoint after deletion of a row.

 The above syntax serves for the creation of the save point only. So, to undo some of the previous group of transactions, here we use rollback command.

ROLLBACK TO savepoint name;

Example:

In the above table, we are left with 4 rows after deleting a row and creating the first savepoint.

Consider the above table.

CUSTOMER TABLE

IDNameAge
2551Ravi23
2553Rinku26
2554Sharma18
2555Shravya20

 Now, try to delete another row from the Customer table and create another savepoint.

delete from Customer where ID = 2555;

Now the table looks like,

select * from Customer;

CUSTOMER TABLE

IDNameAge
2551Ravi23
2553Rinku26
2554Sharma18

Now, we should create another savepoint.

SAVEPOINT sp2;

Delete the third row now.

delete from Customer where ID = 2554;

Now the table has two rows left.

CUSTOMER TABLE

IDNameAge
2551Ravi23
2553Rinku26

So, now let’s bring back the rows with the help of the savepoints which we had created for the Customer table.

Execute the following command.

ROLLBACK TO sp2;

Rollback Complete.

Now if we try to print the table, we will be directed to sp2.

CUSTOMER TABLE

IDNameAge
2551Ravi23
2553Rinku26
2554Sharma18

As we can observe we got the last deleted row back. So, for undo operations performed on tables, SAVEPOINT works efficiently.

Now, execute the rollback to sp1 command, and observe the changes.

ROLLBACK TO sp1;

This will undo the operations performed on the table up to Save point 1 (sp1).

So, now we have the table as,

select * from Customer;

CUSTOMER TABLE

IDNameAge
2551Ravi23
2553Rinku26
2554Sharma18
2555Shravya20

So, we can observe the Rollback operation is done successfully.

Let’s now consider another example for the above table only.

CUSTOMER TABLE

IDNameAge
2551Ravi23
2552Virat24
2553Rinku26
2554Sharma18
2555Priya20

We will now insert a row to the above table.

insert into Customer values (2556, ‘Shashi’, 24);
select * from Customer;
IDNameAge
2551Ravi23
2552Virat24
2553Rinku26
2554Sharma18
2555Priya20
2556Shashi24

So, as we inserted a row, we will now create a Save point over here with the name Insertion.

SAVEPOINT Insertion;

For Updating the row having ID = 2555, we give the following query.

Update Customer set Age = 21 where ID = 2555;
IDNameAge
2551Ravi23
2552Virat24
2553Rinku26
2554Sharma18
2555Priya21
2556Shashi24

So, we can now create an Update savepoint.

SAVEPOINT updation;

So, again if we do not need any updation we can rollback to the Insertion Save Point.

ROLLBACK TO Insertion;

We can get the table which is having changes up to the Insertion Save Point.



ADVERTISEMENT
ADVERTISEMENT