CODEWITHSUNDEEP
mysqlsql
Shafin M
Shafin M

Reputation: 91

SQL Rollback not going back to last commit

I'm having problem with the following in SQL:

SELECT
    *
FROM
    department_dup
ORDER BY dept_no;

enter image description here

Then I ran this piece of code:

COMMIT;

UPDATE department_dup
SET
    dept_no = 'd011',
    dept_name = 'Quality Control'
    ;

ROLLBACK;
SELECT * from department_dup;

But the table is not going back to the last commit

enter image description here

Can anyone please tell me what's going wrong here? Thanks!

Upvotes: 0

Views: 52

Answers (1)

Digvijay S
Digvijay S

Reputation: 2705

By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error

Option 1: Set autocomit off 

SET autocommit = 0

OPtion 2: Use transaction boundaries. 

START TRANSACTION;

UPDATE department_dup
SET
    dept_no = 'd011',
    dept_name = 'Quality Control'
    ;

ROLLBACK;

Upvotes: 1

Related Questions