Before we continue with the course, we're going to start to get into some more advanced functionality and some more advanced functions, and to do that we're going to have to turn Safe Mode off. I am going to show you how you can turn Safe Mode off inside of MySQL workbench.

Now I'm going to do this with the caveat that you need to be very careful when you would do this in a production application. There are many times where a safe mode will protect you against doing all kinds of horrible things. It's a good idea to keep it on in most cases and then just override it and with that in mind.

The way that I do it is I do it right here via a command instead of changing the preferences. If I change the preferences just knowing myself, I'd probably go forget to change it back.

It's important to make sure that that's always maintained and so the way you can turn that off temporarily is by saying set, and then SQL_safe_updates set this equal to zero. What this is going to do is this is going to when it's run it's going to process everything, and it's going too only give a warning.

It's not going to stop the execution, and we can test this out by trying to set all of the guides, e-mail addresses, or something like that equal to the same thing. We can't do it with the e-mail addresses because it's unique. Let's pick out something like addresses that good one.

If I do update addresses and then say set addresses city equal to oops and do not add a WHERE clause.

USE devcamp_sql_course_schema;
SET SQL_SAFE_UPDATES = 0;
BEGIN;
UPDATE addresses
SET addresses_city = 'Oops';

Now don't run that. Make sure that you start with the BEGIN block and add a rollback because we are going to want to roll this back. Now if I run all of this code right here everything worked.

Now notice how even though we did this we didn't get any error even though we didn't add a WHERE clause. Now, what happens if we go and take a look. I'm going to say select all from addresses.

SELECT *
FROM addresses;

Now if I run this code you can see that now the address city is all 'oops'. That's not a good thing and so let's roll it back.

So I'm going to roll it back, and now everything should be good. Yes, we are back to Manhattan, Phoenix, and Queens.

That is how you can temporarily set the SQL workbench and just MySQL in general and turn Safe Mode off when you want to run other queries.

Now before I end this guide, you may ask why in the world we would want to do this. Well, I'm going to show you there are times especially when you're building seeding or population kind of scripts such as populating all of the columns in a database. That this is something that's very helpful and it's required.

In the next few guides, we're going to add a new column to one of our database tables, and then we're going to jump out of safe mode and be able to populate all of the columns in the database very quickly and easily. So I'll see it in the next guides.

Code

USE devcamp_sql_course_schema;

SET SQL_SAFE_UPDATES = 0;

BEGIN;
UPDATE addresses
SET addresses_city = 'Oops';

SELECT *
FROM addresses;

ROLLBACK;