All language subtitles for 003 Updating Single Values and Adding Columns in SQL_en

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 data type.

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.