Modifying data in a table3.5
An update query was shown back in section 3.1 'Inserting data into tables' part of this tutorial. This was a very simple query.
Data can easily be changed in an SQL query. Specific rows can be changed using a conditional statement with the
In SQL, changing or modifying data is done via the
Update all data
Assuming the following table is used as below:
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:
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
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: