Intermediate SQL Tutorial | Updating/Deleting Data

Key Takeaways

what is going on everybody my name is Alex Freiburg and today we're gonna be looking at updating and deleting data in a table now what's the difference between inserting data into a table and updating data insert into is going to create a new row in your table while updating is gonna alter a pre-existing row while the leading is going to specify what rows you want to remove from your table so let's get going with the updating so down here Holly flax does not have an employee ID age or gender now we want to update this table to give her that information so let's do update now we need to specify what table we're gonna be hitting off of so let's do sequel tutorial TVO dot employee demographics so now we're gonna use something called set and set is gonna specify what column and what value you actually want to insert into that cell so let's set her employee ID equal to and it's going to be 1012 and we have to specify which one to do this to because if we ran just this is gonna set every single employee ID to one thousand twelve because we haven't specified that we only want Holly flax is row to be updated so now we have to specify where first-name is equal to Holly and lastname is equal to flex so now let's run this and see what we get so one row has been affected let's see what we got and there we go as you can see the employee ID was updated exactly how we specified it right here so we also want to update age and gender and let's do that in the same query so let's set the age equal to thirty one and instead of using and we actually need to use a comma so let's say age equal to thirty one comma gender is going to be equal to female and let's run this and see we get there you go now let's look at our table and as you can see it was updated to 31 and female so very easy very easy to specify what you want oftentimes tables like this will have a unique key like employee ID is our unique key in this table so I could easily just say where the employee ID is equal to and then you know 1012 so it's an easy way to specify what employee you're trying to update so now let's look at the delete statement the delete statement is going to remove an entire row from our table so let's do delete and we actually need to say from and we have to specify what table we want to be removing this information from so let's do sequel tutorial dot DV dot employee demographics and now we need to specify what row we want to remove so let's do where employee ID is equal to and let's choose a completely random employee ID 1005 so let's run this and see what happens so one rows affected let's look at our table and as you can see one thousand five is now gone now you have to be very careful when you use the delete statement because once you run it you cannot get that data back there's no way to reverse a delete statement so if I had gotten rid of this wear statement and I ran this it would delete everything from the entire table and you could not get that data back so a little trick that I use before I actually run a delete statement is I make it a select statement because you're gonna select everything where the employee ID is equal to let's just do in that form and now when you run this you were gonna see exactly what you will be deleting and now we know that Angela Martin that entire row is gonna be gone if I haven't done that and I just went like this and I wrote delete and I only had this running I would not know that this information is gonna be the only one that's gone maybe I made a mistake down here maybe I accidentally put something in - that wasn't supposed to be in there and now I'm deleting much more than I thought I was actually gonna delete so using the selects naming can be a very good safeguard against so then to lead to leading data that you do not want to delete so that is update and delete thank you guys so much for watching I really appreciate it if you liked this video be sure to subscribe below and I'll see you in the next video