UPDATE Queries
A reminder of the database we will be using:
UPDATE Queries at Higher
At National 5 you are able to use an UPDATE query to alter one or more fields in a database table such as in the example below:
At Higher, you are required to implement some complexity in the WHERE clause.
This could include
multiple conditions,
use of ALIAS
use of wildcards
conditions with calculated values.
The SET clause can be a specific value or an expression. The WHERE clause is optional - if this is omitted then every record in a table would be updated.
UPDATE Example 1 - Multiple Conditions
All RAM with less than 5 items in stock that is £55 or less is to have it's price reduced by 50%.
Example 1 - Query Design
Table to be updated: Item
New Values : price = price /2
Criteria for Updating: stockLevel < 5 AND price <55
Example 1 - Implementation
UPDATE Example 2 - Using ALIAS
You can use an ALIAS in the UPDATE clause to shorten a long table name
This can then be used in other parts of the UPDATE clause
We will use as ALIAS to refer to the table CustomerOrder as c and then use that to update the orderID for OrderID '999' to it's correct value of 'M999'
Example 2 - Query Design
Table to be updated: CustomerOrder AS c
New Values : 'M999'
Criteria for Updating: c.OrderID = '999'
Example 2 - Implementation
UPDATE Example 3 - Wildcards
Any item that has 256GB in its description is to be reduced by 50%.
Example 3 - Query Design
Table to be updated: Item
New Values : price = price /2
Criteria for Updating: description LIKE '%256Gb%'
Example 3 - Implementation
UPDATE Example 4 - Conditions with calculated values
A sale is to be offered on any items where there is more than £3000 of the item in stock. The price of any items matching this are to be reduced by 10%
Example 4 - Query Design
Table to be updated: Item
New Values : price = price * 0.9
Criteria for Updating: (stockLevel * price) >3000