Lesson 6 – Updating Table Records
A database record often needs to be updated. For instance, if the price of a product changes, you have to make that change in the database. This is where the UPDATE queries come to play. The UPDATE query is used to modify existing records in the database.
Let’s jump straight to an example and increase the price of all the products by 10%. Look at the following script:
UPDATE products SET price = price + (price * 0.1)
There are two parts of an UPDATE query. First, you have to specify the table that you want to update. Next, you have to use the SET keyword and assign a new value to the column that you want to update. For instance, in the above script, we are updating all values in the price column, by assigning a new value which is 10% greater than the previous value. Now if you select all records from the “products” table, you will see an increase of 10% in their old prices as shown below:
You can see that the price of “Core i7” has been increased from 800 to 880. Same is the case with all the other products.
In the previous UPDATE example, all the records where updated. You can also update a specific record using the WHERE clause. Let’s reduce the price of “Core i3” by 5%. Look at the following script:
UPDATE products SET price = price - (price * 0.05) WHERE name = "Core i3"
In the above code, we are using the WHERE clause to reduce the price of the product by 5%, where the name of the product is “Core i3”. If you use the SELECT query to retrieve all the products, you should see the following results:
From the above output, you can see that the price of “Core i3” which previously was 440 has been reduced by 5%(22) and the current price is now 418.
In this lesson, you studied how to update an existing record. In the next lesson, you will see how to delete records.