In the last guide we talked about how we could update values and that is very helpful and very important. One thing that you may want to do is if you're afraid that your update may have some negative side effects.

Say you're running a big complex script and you're pretty sure you have the whole thing worked out but you want to have a little bit of protection. In this case, you want to be able to reverse that. Well, this is going to be one of those items we've talked about where it's slightly different based on the database that you're using.

So in other words the MySQL implementation of this might have different syntax than say the oracle or the Microsoft version of SQL. So I'd definitely recommend to just Google the documentation and you will find it.

This is something specific to MySQL and also MySQL that uses the you know NoDB engine in case you're running at a with a different type of installation than I have. So what I'm going to do is I'm just going to create a regular update statement. So I'm going to say I want to update the guides and I want to set the guides_title equal to “oops” and where guides_users_ID is 1.

So now if I just ran this code exactly like this it would change that guide's_title and there would be no way of reversing that. So that may or may not be something that you want to do.

How can we protect ourselves against this? Well if you come up and you type “BEGIN” and then a semicolon at the top of your script. Then you run all of this right here you can see that it was all run properly. Now if I say SELECT * FROM guides and if I run this code you'll see that the title now says oops.

Which in this case was not what we wanted but we can actually because we use this begin block here we can reverse this. The way we can is by saying ROLLBACK;. Now if I run the rollback code and then run this select statement again, you'll see that this has now been switched back.

So this is something that's pretty helpful especially if you're doing some things that are experimental. You want to make sure that you have the ability to backtrack because by default many database engines do not have what's called a “transaction history”. A transaction is a register where the database engine can see everything that it did.

If it has transactions then if you do something like we just did with rollback. Then it makes it possible to say “OK I want to go back to that previous transaction and I want to restore state to that spot”. If you do not use BEGIN and ROLLBACK or if you're using a database that doesn't allow for transactions.

Then you're not going to be able to do it and then you have to be even more careful with your queries. This is something that is very important especially as you're learning. This is something that can be considered a best practice when working with SQL. Being able to make sure that you have a way of reversing any actions that you take.

Code

USE devcamp_sql_course_schema;

BEGIN;
UPDATE guides
SET guides_title = 'Oops'
WHERE guides_users_id = 1;

SELECT *
FROM guides;

ROLLBACK;