Advanced Database – Create, run a query to update data in a table.

If we notice that there is an error with certain data, sometimes it is easy to change it using the ‘Update’ command.

Imagine we notice that Part Ref ‘D1’ (spark plugs) can only be bought in packs of 10, but many records show less than 10 – a human error.

We can correct this using the UPDATE command in SQL. Close the table.

Select Tools -> SQL from the main ribbon to open ‘Execute SQL Statement’ window.

In the Command to execute section type: UPDATE “Order Details” SET “Amount”=’10’ WHERE “Part Ref”=D1

Note that SQL uses colour codes: Commands are blue. Table names, fields and records are orange. Mathematical operators are black. Also, use “” to refer to tables and fields and ‘’ to refer to text values.

This command will update the Order Details table setting the amount field to 10 whenever there is a D1 in the Part Ref field.

Click ‘Execute’ to run the query.

Status will state ‘Command successfully executed.’

Close the execute SQL window.

Open the Order Details table.

Notice that Order Ref 210 amount has changed from 3 to 10 to reflect the update we performed.

This is a useful feature to use if you have large amounts of data that needs changing. For example, a store raising all of its prices by 10% could use the update function to quickly change records.

In general, to update a record in a table use the SQL syntax:

UPDATE name_table SET name_column=value WHERE some_condition

NB. some_condition is often a mathematical statement such as =8 or >15 or it could be applied to a text field (column) =‘Yes’, for instance.