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:
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.
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:
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 |