In this guide, we're going to walk through how to add a new column in MySQL. Then also how to populate it with data. So coming to our guides table right here if I click on the little wrench this is going to pull up our spot where we can change any of these values, and we can also add new ones.

That's what we want to do. Coming down to the very bottom I'm going to say guides_quantity I'm just going to say a qty. For this quantity, I want to go with just an int value, so I am going to click on int and let's say that we want this to be non-null this should be required field. So now if I click apply.

This is going to say that it wants to alter the table and add the column with guides quantity to not null. That should be everything we need, and if I hit apply everything worked. Now if we come back to our query interface and type select all from Guides and run this query.

SELECT *
FROM guides;

You can see that now we have guides quantity. Now they are all zeros right now just because they're integers. This is the default value because we said not null. Now, this is not very helpful, so we need to populate data. We're going to learn not only how to populate data but because we are in the functions section. Let's leverage a very cool little function called Rand to populate that.

What we're going to do is we are going is turn safe mode off. So we're going to say set sql_safe_updates set this equal to zero and coming down. I am going to add a BEGIN block just in case we do something wrong, and we want to rollback, and then I'll say update, and we want to update the guide's table.

I want to set the guides quantity equal to Rand and then give parentheses times a thousand. Let me also add a rollback just in case. What this is going to do is it's going to update guides, and it's going to go to each one of the records, and it's going to update the new quantity column that we just added. It is going to create a random function, or it's going to call the random function multiplied by a thousand.

It's going to give us numbers that are randomly picked out by the computer, and it's going to put those inside of each one of these. Each one of these items should be different, every once in awhile the random number generator is going to fall on the same ones. For the most part, they are going to be unique. So I'm going to select all of this, and I'm going to run it

That looks like it worked, let's come and select all from guides just to make sure. If I run this that looks like it did not work. So what exactly happened? Let's see. So this is interesting. So when I highlighted it, I believe it only ran the one command that I highlighted on the last part I selected. Let me start this over again. Hit the little lightning bolt this time.

I believe that that one worked. Looking down at the response it says six rows were affected and rows matched changed with no warning.

Now if I come back here and run it.

So that you know what happened there. My little command the command slash shortcut. Apparently, it only seems to work on the last line that gets selected. It only ran this turn off mechanism here it didn't run all of them. Just kind of keep that in mind whenever you're trying to select multiple statements. So with all of that said everything works. So now we have guides quantity, and we have all of these guides.

Notice how we didn't have to do anything. We were able just to call this random function multiply it by a thousand which means that it's going to give us any numbers between one and a thousand. You can see that's exactly what it did right here and it populated it for us.

Now this is not always something that is going to be that useful depending on if you're working with production data. There's a huge percent of the time you're going to be spending building in prototyping applications. For that, you're going to need a lot of sample data.

I promise you don't want to create a thousand records and do all of the data entry and manually. You're going to want to find ways that you can automate it kind of like how I did with the user's table. Right here this is a great way of being able to populate an entire column. If this database table had 5000 records, it would have done the same thing.

We wouldn't have had to go and type a set of random numbers in for every single one. The script did it all for us. This is a very helpful way of updating all of the records and populating them in SQL. Now with our new quantity set in the next guide, we're going to walk through how we can run computational functions on top of that.

Code

USE devcamp_sql_course_schema;

SET SQL_SAFE_UPDATES = 0;

BEGIN;
UPDATE guides
SET guides_qty = RAND()*1000;

SELECT *
FROM guides;

ROLLBACK;