Hands on: Using update queries in a database

We often use queries to extract subsets of data, but they can do so much more.

Perhaps you want to increase by five per cent the price for all items with a product code starting with ‘KP’, or you want to delete all records where the customer’s name is ‘Smith’.

These operations don’t just return what’s already in the tables; they perform some action on the data, making changes to what’s stored in the database.

It’s a slight oddity of the relational model that such operations are treated as queries.

In Access, for example, the Query Type button on the menu bar offers a dropdown list of six query types: Update is the one we’ll concentrate on here.

Mark Thornton emailed on this topic: he has a table of 100 rows and wants to update the values in Field A by adding the value in Field B to them.

In another table of 3,000 rows, he needs to decrement the value in Field C by one.

These are perfect operations for illustrating the joys of, in SQL terminology, Update queries. (The standard queries that just return a set of data are known as Select statements or queries.)

Imagine we are working with a 100-row version of the Item table in screen 1 .

If you are doing this for real, you will of course be working on a copy of the table: update queries change data so it’s important you check that updates behave in the manner you require before performing them on your tables.

We want to add the value in Field B to that in Field A, which would, for the row with ID 4, give us the value of 4 in Field A.

Open a query grid as usual and add the Item table, including Field A. Now click the down arrow alongside the Query Type button (showing the same glyph as the Query tab in the database window) and select Update Query.

The query grid changes to give a row labelled ‘Update To:’. In the Update To row for Field A, enter:

[Field A]+[Field B]

which simply says add the contents of the two fields ( see screen 2 ). The SQL reads:

UPDATE Item SET Item.[Field A] = [Field A]+[Field B];

Clicking the View button shows the existing values in Field A, which will be updated when we run the query.

To perform the update, return to the query grid and click the Run button (red exclamation mark), whereupon Access warns that you’re about to update rows and that, once performed, updates cannot be reversed with the Undo facility.

Click OK to go ahead. Inspect the table ( see screen 3 ), and the Field A values have indeed been updated as required.

The second operation – decrementing a value by 1 – is performed using another Update query, shown in screen 4, and the SQL is:

UPDATE Item SET Item.[Field C] = [Field C]-1;

Access issues a warning when you run the query, and when you go ahead the table is updated as required ( see screen 5 ). Download the DBCJUL06.MDB database.

Mark says he’s presently working with a solution that uses VBA querying, but it is very slow. That’s not surprising: VBA provides a sequential, procedural solution that will pull out a single row, do whatever is necessary and then move on to the next row.

Because it handles rows one at a time, it is indeed slow. Writing the same queries in SQL adds speed – lots of it – because SQL is a set-based language.

It is designed to handle sets of data; whole bunches of records at once rather than a record at a time. You give SQL a set, specify what you want done, and it’s carried out on the whole set at a single stroke.

Of the query types offered by the Query Type list, Update, Delete, Append and Make-Table are all known as ‘action queries’ in Access, because they perform some action on your data.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!