Now, in the last lesson, we looked at how you would read data from your database and how you would select
pieces of data based on certain criteria.
In this lesson we're going to tackle the 'U' in CRUD which is how do you update records in your database?
So at the moment if we look at the entire products table, you remember that the price for our pencil
is set as Null, because when we created this record we didn't have a price yet.
So we left it blank.
If I wanted to update that record to now provide a value for the price of my pencil then I can use the
UPDATE statement.
And this is how the syntax looks.
We use the UPDATE keyword and we specify the table we want to update, and then we SET the values that
we want to update, and we can specify a particular condition to only update the records that we were
interested in updating.
So in our case, we would write something like UPDATE, and the table we want to update is of course, again
the products table, and then we write SET,
and we want to set the price to equal 0.8 as the price of our pencil.
Now this is quite a dangerous statement because if I hit Run right now, it's going to set everything
in my table to have a price of 0.8.
So it's really important that you specify the WHERE statement so that it only sets the price of
0.8 where the record has an id equal to 2.
So now it's going to find this particular record and only set its price to 0.8.
So now if I hit Run, and let's see our products table again, you can see our pencil now has a price and
we don't have any null values anymore.
Now what if you wanted to change the table instead of a particular record? Say in our product table,
what if we also wanted to keep track of the stock of each of our products? Well, then we would need to
add a new column. In SQL
that means we need to alter the table.
And this is the ALTER table syntax.
It's used to add, delete, or modify columns in an existing table, which is exactly what we want,
right?
And this is what you need to write.
ALTER TABLE, provide the table name,
and then you add the column name and datatype.
So over here we would say ALTER TABLE and the table is the products table and then we say, ADD the column
name that's called stock and then the data type will be simply an integer.
So now if we hit run and we view our products table you can see we now have an extra column tagged on
at the end but they both have Null values.
So as a challenge, I want you to update both of these records so that they will have a stock value and
the value of the stock should correspond to what you see in this table.
So we have 32 pens and 12 pencils to sell. Pause the video and try to complete this challenge.
All right.
So there shouldn't have been too hard because you can always head back to the documentation and check
out the syntax for the UPDATE statement. In our code
we need to specify UPDATE the products table
and we're going to SET the stock to equal 32 for the pen and we're only going to set the stock to 32
WHERE the id is equal to 1.
So now if we hit Run, then you can see our Pen now has a stock value and the Pencil has a stock of 12.
So let's clear the screen and let's UPDATE the products table and SET the stock to equal 12 WHERE the
id is equal to 2.
And now we have stock values for both of our products and our table is now complete without any null
values.
But you can start to see how painful it can be
updating some of these values and especially when you haven't accounted for it when you created the
schema.