Multi-Table Update Query in MS Access

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering and data analytics in this episode we're going to return to our microsoft access playlist we're going to talk about update queries and how to use those in various scenarios the first one we're going to show you is a very simple update query which is on a single table where you can say i want to update these categories where they currently are equal to this and then i'm also going to show you an example that uses multiple tables so that you can update one table from another table by joining them together so without further ado let's get to our microsoft access update queries okay so to get started we're looking at a database that we used a few times and in our last uh example for the append query we worked on some candy and candy orders and in this example we're going to do some updates to our candy table what i'm going to do is i'll make a copy of our candy table and i'm going to call it candy new prices and we're going to say that's a table that somebody gave gave us one of the you know updates came down from the executives and they want to change the the cost of the candies they want to start charging more money so so here we go we're going to charge more money for the candies and [Music] what we're going to do is we're pretending this is coming down and this is just something that we have to update and our original table the candy table that's a production table and you know we can't mess around with that too much we can just run an update on it and that's it and it might have you know like tens of thousands or hundreds of thousands of rows in it and this is just our update for these few so um there's our candy table as i said this is our production table and so uh we want to make sure that these values get updated uh like i said this could be a table with with you know tens of thousands of rows and we only want to update a few uh prices and in that case that's something that we're going to do so in fact what i'll do is in our new price table i'll i'll get rid of the last three rows just so that um just so that only some of our items get updated and you can see that kind of reasoning in action and so that's our new prices in one table that we have to somehow get into our production table which is the candy table so before we go ahead and update prices what we're going to do is we're going to take a look at gumballs and we're going to actually do a single column update which is going to be the simple example so we're going to say that gumballs in our candy table we're not allowed to call them gumballs anymore we're gonna call them gum circles because somebody had a bright idea that they wanted to call them gum circles and so we'll go to our candy table and we can drag that on there this is our production table and the the candy type is gumballs and we're not allowed to call them gumballs anymore we have to call them gum circles so what you do is you can drag your column onto here and then you can click the update you can click the update on the ribbon there to change it to an update query you can do that pretty much anytime during selection here so we've selected candy type and we're going to type in we're going to update it to gum circles from gumballs and and this is going to be something that somebody needed to have done for one category and this is the most simple example so where it's where our rows have gumballs in them we're going to update it to gum circles and this is sort of like the simple the simple way of doing a single column update so you've typed in your update 2 and you've typed in your criteria and so now we can control s or you can hit save up above and we'll call this gum gumball update and and then we can sort of see how that goes actually i've typed i've got a filter on here called candy so maybe we'll go and look for our gumball because there's so many objects in this little test database and we'll rename that actually to gumball candy update so you can right click and rename it and uh and then gumball candy update and then that'll keep our candy objects all together so i'll type in my candy filter and there we go there's our gumball candy update okay and as i mentioned here's our production table called candy and there's our gumballs it looks like there's three rows with gumballs in it and if i go to the design of our candy update that's where it looks there now you notice that it's not it is not case sensitive and so it will update it if i double click double click on the gumball candy update it says you're going to update and then it tells me how many and then and then that query has executed and now if i look in my production table here you can see it says gum circles where it said gumballs before and that is sort of how we can do a single column update you can actually do multiple columns but it gets a little tricky after that but you can try that out on your own so what about if we wanted to update a whole bunch of values say we had new prices for our candies like we have in our our new price table that we created in the beginning and here's the production table with the lower prices and we want the prices from the other table to to be put into this table according to the id so we're going to use the id column because we can see we were very lucky they use whoever sent the list they use the proper ids so we know we know that we can use the id to join them sometimes you have to use the type and description or something like that to make your own join but in this case we have a nice id column and so in order to say you know update this one with just the one from the other table we join it based on the id and uh there's our type that we just updated but actually though those will be different because we ran an update query on those already so we're just using the id which is gonna look at each distinct row and we're gonna grab our candy price double click on candy price from the candy table and as i mentioned before you can click on the update in the ribbon above and that will expose the update to and criteria for you and in order to specify what we want that join is going to say you know this row equals that row in in the other table and what we can do is we'll start typing in our square brackets here and it'll auto fill to our other table the candy price new price table and then we'll choose the candy price field so we're going to update this table to all the values in the other table and uh for the price and since we've got our join there it's only going to update the the rows that have the right ids on them and so that makes it nice and handy so that we can update those so we'll give this this query a name as well and you should also note that each of those tables must be updatable so in order for the query to work so sometimes it won't work if you have like a really complicated query that you want to update you need to just put that query into its own table do a make table and then run the update but in our case both tables are updatable we can double click on candy price update it says we're going to update seven rows and then we can look at our results and you can see that only seven rows of our update pardon me only seven rows of our production table were updated and the rest of the rows remain with their original prices so there we go there's our new prices in that table which have all been selected the new prices and they've been put into the production table as you can see here and that is how you do an update query in microsoft access hope you enjoyed today's discussion on how to do update queries in microsoft access if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel if you have not subscribed yet click the bell and leave some comments in the comment section below if you have any questions have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 1,007

Rating: 5 out of 5

Keywords: Update multitable query MS Access, access update query, access update one table from another, ms access sql, access single table update, access multi table update, Sean Mackenzie Data Analytics and Data Engineering

Id: 9CBxtBylSvE

Length: 10min 7sec (607 seconds)

Published: Tue Mar 30 2021