So you know how to insert records into a database, query, filter, limit, and how to offset records. Now let's talk about what we need to do when we want to edit records. The next command that we are going to use is update.

In order to update records we need to say update and then give the name of the table. So when you say UPDATE users and then you can SET the parameters. I can say SET users_email and set this to update@test.com. Then the next thing we have to do is pick the user or users that we want to update.
So here I can say WHERE users_id = 2.

If I were to run this then it would update it. Before I do that let's actually take a look at this data and say SELECT * FROM users WHERE users_id = 2. Now if I run this query just so we can see it.

Right now the email address is Tiffany@test.com.

Now if I run this query and run it and nothing gets returned but down below you can see that this ran successfully. If I rerun this query again you can see that now the email address is update@test.com. It has effectively changed it from where it was before, to where it's at now.

Now because we're using “where” this may give you a little bit of a hint that we can also extend this. So as nice as it is to be able to pick out one item it's also possible to pick out as many items as you want. You can use either “OR” or the “AND” syntax. Now one thing I will tell you if you use or and you're picking different parameters that would make it considered a kind of a bad idea.

When I say bad idea I mean say that you tried to do an “OR” that essentially would have accidentally updated all of the records in a database. Then mySQL workbench is going to block you from doing that. That is because by default mySQL workbench utilizes what's called “Safe Mode” and safe mode tries to protect yourself from yourself. It tries to protect you from accidentally deleting or updating all the records in a database.

I remember there is one time when I was originally learning SQL and I did code that was pretty much exactly like this. When I did it I didn't put a “WHERE” clause and what ended up happening is it didn't just update a single user, it updated all of the users in the database.

Thankfully it was a very small application. This was about 15 years ago and so it wasn't a very big deal to reverse it. In the next guide, I'm going to walk there and show you how you actually can reverse that rather efficiently. But for right now just know that you want to make sure that you're always putting this “WHERE” context in place.

Now if you did want to update all of the records in the database in you are in MySQL workbench you just need to switch out of safe mode. I'll show you how to do that later.

Right now let's just be concerned with having some safe queries. So we have this one which we saw change the value. Now let's see how we can update and look at either multiple values or narrow down our focus. So if I do SELECT * FROM and let's go with the guides table this time. If I say select all from guides this is going to return all of our guides. You can see that we have a guides_title, users_ id, and we have guides_revenue.

What I want to do is I want to say update and remember the first item we do is the name of the table. So I'm going to say update guides and then I can say “SET”. Then I can pick out what I want to change. I can say I want to change the guides_title and I just want to change it to “something else”.

This is going to show that something did get changed in the guides_title. Now let's give our “WHERE” parameter. I'm going to say where and let's pick out the guide's_title and to change where this is to “another post”. Then we can also say AND the guides_users_id is 2.

So imagine a scenario where we want to change a title in a database and instead of just changing all of them that match one title. We want to say that we only want to perform that action for a specific user. So what this is doing by leveraging this “AND” command here is we're narrowing the focus down on our query.

So we're saying not only does it have to match the title, It also has to match the user_id. Now this kind of goes into why you'd need to be careful if you did in “OR” here. If you did an “OR” what would happen is, it would look for all of the titles that have another other posts. Then also all of the users_id that are 2 and then it would change the title for all of them.

That's a reason why you can only operate that in the in the non safe mode because you might have some side effects that you may have not anticipated. So let's just highlight all this and run it. It looks like all of that worked. Now if I go to “SELECT * FROM guides” you can see that that worked perfectly and It now says “something else”.

Now the cool thing about this is if I were to copy this, change it, and say that we want to update this to “something else again”. Wherever it says “something else" we need to update that. So this one is targeting here but I've changed the users_id to be one. So that means this should not work because it's not going to find any matches.

There are no records that have a guides_title and a user_id of one that match these criteria. So if I select all of this and run it you can see that it seems like everything worked in it and It didn't have any errors.

Which is good but if you come down to the response right here it shows 0 rows affected. There's no rows were matched and so none were changed.

We had no warnings and if we come back and run a query on the guides table again. You can see nothing got changed.

That's kind of the power of being able to leverage multiple filters and be able to add “AND” queries on top of your initial “WHERE” ones. So that is how you can build an updater-script in SQL.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM users
WHERE users_id = 2;

UPDATE users
SET users_email = 'update@test.com'
WHERE users_id = 2;

SELECT *
FROM guides;

UPDATE guides
SET guides_title = 'Something Else'
WHERE guides_title = 'Another Post'
AND guides_users_id = 2;

UPDATE guides
SET guides_title = 'Something Else Again'
WHERE guides_title = 'Something Else'
AND guides_users_id = 1;