UPDATE Queries

A reminder of the database we will be using:

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.

All RAM with less than 5 items in stock that is £55 or less is to have it's price reduced by 50%.

Table to be updated: Item

New Values : price = price /2 

Criteria for Updating: stockLevel < 5 AND price <55

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'

Table to be updated: CustomerOrder AS c

New Values : 'M999'

Criteria for Updating: c.OrderID = '999'

Any item that has 256GB in its description is to be reduced by 50%.

Table to be updated: Item

New Values : price = price /2 

Criteria for Updating: description LIKE '%256Gb%'

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%

Table to be updated: Item

New Values : price = price * 0.9

Criteria for Updating: (stockLevel * price) >3000