Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 3.5Modifying data in a table

Part 3.5Modifying data in a table

An update query was shown back in section 3.1 'Inserting data into tables' part of this tutorial. This was a very simple query.

Modifying data

Data can easily be changed in an SQL query. Specific rows can be changed using a conditional statement with the WHERE clause.

In SQL, changing or modifying data is done via the UPDATE keyword.

Update all data

Assuming the following table is used as below:

Product_ID type quantity
32 Apple 10
45 Orange 91
78 Apricots 25
141 Pear 34

If every month the shop clears its sales figures (as in the quantity field is cleared), then the quantity field needs to be set to 0 on all products. This is easily achieved with:

SQL
UPDATE `fruits` SET `quanity` = 0
		

The resulting table would look like:

Product_ID type quantity
32 Apple 0
45 Orange 0
78 Apricots 0
141 Pear 0

Every quantity field in every row is now set to 0.

Updating specific rows

Of course, on most occasions it would be more desirable to update only a specific row or a specific set of rows. This can be achieved using conditions.

The following query will update the database where the quantity of products is greater than 50 to the value of 0.

SQL
UPDATE `fruits` SET `quanity` = 0 WHERE `quantity` > 50
		
Product_ID type quantity
32 Apple 10
45 Orange 0
78 Apricots 25
141 Pear 34

Increment rows

The update query can also increment (or decrement) each row simply by using the following query:

SQL
UPDATE `fruits` SET `quanity`=`quantity` + 1
		

The result of this query would be:

Product_ID type quantity
32 Apple 11
45 Orange 92
78 Apricots 26
141 Pear 35
Feedback 👍
Comments are sent via email to me.