When it comes to queries I can't stress the importance of the value of being able to dynamically inject data into your queries. In this guide, I want to walk through a very practical way to use subqueries with insert statements. Let's say that we want to add to our users database. I can say SELECT * FROM users and you can see that we have a ton of users.

Say that we either want to add a new user or we wanted to add a guide but use a specific users_id this might get a little bit messy. Take for example a situation like this where we have all of these users and I create a new user. So just for practice I definitely recommend you follow along either now or after the video and insert a new user.

You say insert into users and we need a user's name and users email. INSERT INTO users(users_name, users_email) and now we give the values for those. So the names I'm going to use is John and for the email is John@snow.com. VALUES(john, john@snow.com) now if I run this query it's going to run.

One row was affected and everything worked. Now let's say that we want to have John write a guide. Well, how do we know what John's id is? Remember we're going to need to be able to know what his users_id is in order to inject it.

When we create the guide remember our guides have a users_id column and that's how we set up the data relationship and map the two tables together. Well, there is one way of doing it. I mean you technically could just say SELECT * FROM users just like we did on line 3. Then run this query and then you'd have to go all the way down the list. You'll see we have John and his id is 2006.

That would be a very poor choice when it comes to doing this, especially in a professional environment. Instead, this is where you can use a subquery.

What we can do is I can say INSERT INTO guides and I can say guides_revenue guides_title, guides_users_id, and guides_quantity. INSERT INTO guides(guides_revenue, guides_title, guides_users_id, guides_qty)
Now one thing to note is I have come back after filming the entire course to and this guides_quantity.

If you do not have the guides_quantity at this stage of the course then you can just skip over that one. In this case, I'm not sure what stage I added the quantity and so I want to make sure that it doesn't confuse anybody. Now that we have this insert statement now let's decide on what our values are going to be.

So for the guides_revenue, we can put any number we want. So for the guides_title, we can just say a “guide by John”. Then the guides_user_id This is where things are going to start to get a little bit trickier.

Let's bring this down to a couple of lines just to make it easier to read. Now instead of hard coding this. Technically you know that the id is 2006 but let's imagine we're in a database where we don't know what the id is. It would be a pain to go and manually check it, what I can do is run a subquery that is SELECT users_id FROM users WHERE the users_name = John LIMIT 1

Now technically in a production environment, you wouldn't search by the user's name to grab the ID because it's a very good chance you're going to have duplicate names. Usually, you search by says their email or something that would be unique. For right now just to show that you can search by anything I'm gonna do that. Then the last thing for the quantity I'll just put 300 and I'm going to close this query out.

Essentially what I'm doing right here is I'm injecting hardcoded values just like normal for the revenue, title, and then the quantity. In the middle, I'm saying I don't know what this value is and SQL I want you to bring it to me. So what’s going to happen is this query is going to run as soon as it gets hit.

It's going to grab the user_id because remember that's what we're querying. It's going to grab that ID in it's going to slide it right into that slot. So it's going to be the exact same as if I wrote this and I said “2006” just like that. This is though a much more dynamic way of doing it.

Now if I run the query everything worked it shows one row was affected. Now if I go SELECT * FROM guides and run this query.

You can see that we now have a guide by John right here. That is something that I wanted to show you because I really feel very strongly about how you can work with subqueries. Mainly because I've been using them and I need to use them on such a regular basis in production based environments.

So I know once you get out there and you're building production databases you're running reports. This has going to be something that you're going to have to call on quite a bit. So I recommend that you go through it play around with how else you can use some queries so they can really become familiar with them.

Code

USE devcamp_sql_course_schema;

INSERT INTO guides(guides_revenue, guides_title, guides_users_id, guides_qty)
VALUES(
  500,
  'Guide by Jon',
  (SELECT users_id FROM users WHERE users_name = 'Jon' LIMIT 1),
  300);