Action Queries: Update Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but make changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. Last time, I discussed the Delete Query. This time, I'll look at the Update Query.

Update Queries

An Update Query will make bulk changes to records in the record source. . The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.) At its simplest, an Update Query will make changes to every record. For instance, suppose I want to raise the Price of every book in the Books table by 10%. I could create a SQL statement like this:

UPDATE BOOKS SET Price = Price*1.1;

In the Query Builder, it looks like this:

But it's not very often you want to update every record. Usually, you want to update just certain records. To do that, you add a Where condition just as you would in a Select Query (see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to change every book whose price was $25 to $24.95. My SQL statement would be as follows:

UPDATE Books SET Price = 24.95
WHERE Price=25;

Which looks like this in the Query Builder:

Of course, you could also raise the price by 10% for a particular publisher:

UPDATE BOOKS SET Price = Price*1.1
WHERE PubID=2;

Updating from a List

But what if, as I did with the Delete query, you are given a list of ISBN numbers and associated new prices? How could you update your Books table from this list? You SHOULD be able to use a subquery. Something like this:

UPDATE Books SET Books.Price =
(SELECT price FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN)
WHERE ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN);

Unfortunately, while you could do this in almost any other SQL implementation (SQL Server or Oracle, for instance) you cannot do this in Access SQL. It will give you the "Operation Must Use an Updateable Query" error.

Fortunately, there are a couple of workarounds. One is to use a Join and the other is to use a Domain Aggregate function.

Workaround 1 - the Join

This work around requires you to Join the Books table with the UpdateList table, like this:

UPDATE Books INNER JOIN UpdateList
ON Books.ISBN = UpdateList.ISBN
SET Books.Price = [Updatelist].[price];

In the QBE:

One caveat here is that the Join MUST be an updateable dataset, so there must be a unique index on the Join field (in this case ISBN).

Workaround 2 - DLookup

This work around uses a subquery in the Where clause, but uses a DLookup function in the Set clause:

UPDATE Books SET Books.Price =
DLookUp("Price","UpdateList","ISBN ='" & [ISBN] & "'")
WHERE Books.ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = updateList.ISBN);

This solution does not require a unique index, but it is much slower than the Join solution.

Running an Update Query

There are two buttons for running Action Queries The View button displays the results of the query, that is, which records will be updated. The Run button actually updates the records.

Next up, the Append Query.

Action Queries: Delete Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. I'll start with Delete Queries and discuss the rest in subsequent posts.

Delete Queries

As the name suggests, Delete Queries delete records from a record source, that is, a table, a join, or another query. The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.)

At its simplest, a delete query will delete all records from the table:

DELETE * FROM Books;

But in most cases, you probably want to delete some subset of all the records. For that, you restrict your records with a Where clause, just as with a Select Query(see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to delete all the records from a particular publisher:

DELETE * FROM Books WHERE PubID=1;

In the Query Builder, I generally suggest creating a Select Query for the records you want to delete:

And then convert it to a Delete Query using the Ribbon (or Toolbar in Access 200X):

Now, suppose you are given a list of ISBN numbers that you need to delete from your table. If that list is in a table (DeleteList), you can delete the records from your Books table with a subquery using the IN operator, like this:

DELETE ISBN FROM Books
WHERE ISBN In (Select ISBN from DeleteList);

In the query builder, it looks like this:

The Select subquery will create a list for the IN operator to supply ISBN numbers to the Delete query.

Running an Action Query

Running an Action Query is a little different than running a Select Query. With a select query, the View and Run buttons do exactly the same thing: display the results of the query. With Action Queries, however, the two buttons do different things. The View button displays the results of the query, that is, which records will be deleted, updated, inserted, or appended. The Run button actually executes the action.

Next time, I'll discuss the Update Query.

.

What Is A Join Part 5: Outer Joins

Outer Join (Left Join or Right Join)

So far in this series (What Is A Join: Part1, Part2, Part3, and Part4), I've concentrated on the Inner Join, which is the most common type of join. However, the Inner Join has a limitation. It will only match field values that exist in both tables. Going back to our Order and OrderDetails tables:

Orders
OrderID	OrderNumber
1	111
2	222
3	333
4	444

You can see that there is an OrderID "4" in the Order table.

OrderDetails
OrderDetailID	OrderID	Quantity
1	1	1
2	2	2
5	3	3
6	1	2
7	1	1
8	3	2
9	3	1

However, there are no records for OrderID "4" in the OrderDetails table.

Query1
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

Thus, OrderID "4" does not appear in the resulting Inner Join. But there times when you want to show all of the records of one table and the associated records in another, whether or not all of the records in the first table have a match.

This is called an Outer Join. In Access, there are two types of Outer Joins, Left Join and Right Join. The Left and Right refer to which side of the equal sign the table is on in the JOIN clause. I'll get back to that in a minute.

To create an Outer Join in the Query Builder, start with a standard Inner Join. Then right click on the Join line, and select Join Properties You'll get a pop-up box with three choices:

Selecting either Option 2 or 3, will create an Outer Join. If you select 2, you'll be creating a Left Join. If you select 3, you'll be creating a Right Join. The result will look like this:

And the resultset will look like this:

LeftJoin
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1
444

The SQL for this query looks as follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

The "Left" and "Right" handedness doesn't really have anything to do with the table positions in the Query Builder. It has more to do with the positions of the tables with respect to the JOIN keyword.

An Inner Join will look like this:

FROM Order JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

Since it is joining only matching records, it doesn't matter what order the tables appear in the clause. But to create an Outer Join, you have to indicate which table will show all records. In Access, you do that with LEFT JOIN and RIGHT JOIN.

FROM Order LEFT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Left Join, the table on the left of the JOIN keyword (Order) will show all records.

FROM Order RIGHT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Right Join, the table on the right of the JOIN keyword (OrderDetails) will show all records.

Multiple Joins with Outer Joins

An outer join (left or right) can participate in a multiple table join only under circumstances. If the query also involves an equi-join and certain outer joins, it will result in the Ambiguous Outer Join error. For more information about this error, see my post: What is an Ambiguous Outer Join?

One last thing about Outer Joins: Earlier, I discussed creating an Equi-Join in the Where clause, but as far as I know, in Access you can't create an Outer Join that way.

Next time, I'll look at a specific application of the Outer Join: the Unmatched Query.

What is a Join: Part 4 (Equi-Joins in the WHERE Clause)

Equi-Joins in the WHERE Clause

In my last post in this series on the JOIN (What Is A Join: Part 3 (Cartesian Joins)), I discussed the Cartesian Join, which has very few uses and in most cases should be avoided at all costs. However, there is one practical use for a Cartesian join as long as you restrict its output with a WHERE clause.

So far, I've created the Joins in the FROM clause of the SQL statement. This is standard SQL, but it's also possible to create an Equi-Join in the WHERE clause. As a general rule, it's better to create your join in the JOIN clause, but there are circumstances under which is it useful to do it in the WHERE, which I'll show in just a bit.

To create a Join in the WHERE clause, you create a Cartesian join and add a WHERE clause equating the common fields of both tables. In the Query Builder, add both tables to the table window, but you DON'T create a Join line between them. If the Query Builder adds one automatically, delete it. In the WHERE clause you make the join field of one table equal to the join field of the other.

For instance, if I wanted to create this query:

in the WHERE clause instead, I would do it like so:

Notice there is no Join line between the tables and the OrderID of the OrderDetails table is equal to the OrderID field of the Orders table. The SQL statement looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID)=[Orders].[OrderID];

Earlier, I showed that without the Where clause, I would get 28 records. However, with the Where clause, I get the same result as I did when I created the join in the FROM clause.

Where Clause Join
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

In general, this is not as efficient as creating the Join in the FROM clause.

Joining fields of different data types

Suppose the join fields in my two tables are different datatypes. Suppose OrderID in the Order table it is numeric and in OrderDetails it is text. This can happen when dealing with external data sources over which you have no control. You can't create a join on fields of different datatypes, so you have to use a conversion function to convert one of the fields. Since OrderID in the Orders table is a Long Integer, I can use the CLng function to convert OrderID in the OrderDetails table.

I can change my original Join clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

As follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders INNER JOIN CLng(OrderDetails)
ON Orders.OrderID = OrderDetails.OrderID;

This will work. However this cannot be represented in the Design View of the query. If you ever open the query in Design View, it will give you an error and remove the Join line altogether. You must close the query without saving or your query will be ruined.

But if you create the Join in the WHERE clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID = CLng([Orders].[OrderID]);

This can be represented in the Design View.

Join Date/Time fields that have times

I have also seen cases where a DateTime field in one table held just the date value, but held date and time in the other table. I needed to use the DateValue function to remove the time from the DateTime in the one table. Again, it was external data over which I had no control.

Creating the Join in the WHERE clause solved the problem nicely. Something like this:

SELECT Patient.MRN, Patient.DischDate,
Billing.Charge
FROM Patient, Billing
WHERE DateValue(Patient.DischDate) = Billing.DischDate
AND Patient.MRN = Billing.MRN;

In my next post, I'll look at Outer Joins.

 

[top]

What Is A Join: Part 3 (Cartesian Joins)

Cartesian Joins (Cross Joins)

This is part 3 of a multi-part series on the SQL Join. Last time I talked about the most common type of join, the Equi-Join (see: What is a JOIN: Part 2 (Inner Join)). This time, I'm going to talk about the least common join type: the Cartesian or Cross Join.

Cartesian Joins are notable mostly for being avoided. A Cartesian Join matches every record in one table to every record in another. Except in very rare instances, this is something to be avoided at all costs. Since it joins all records from both tables, the resultset of a Cartesian join can quickly become huge. A Cartesian join of two tables, each holding a thousand records will result in a query displaying a million records (1000 x 1000). Cross-joining two tables with a hundred thousand records each will exceed the 2GB limit of Access.

A Cartesian join is most commonly created in the Query Builder by neglecting to add a join line between the tables.

Since Orders has 4 records and OrderDetails has 7 records, the resultset of the Cartesian join will have 28 records:

Cartesian Join
OrderNumber	OrderID	Quantity
111	1	1
222	1	1
333	1	1
444	1	1
111	2	2
222	2	2
333	2	2
444	2	2
111	3	3
222	3	3
333	3	3
444	3	3
111	1	2
222	1	2
333	1	2
444	1	2
111	1	1
222	1	1
333	1	1
444	1	1
111	3	2
222	3	2
333	3	2
444	3	2
111	3	1
222	3	1
333	3	1
444	3	1

 

As you can see, the information is fairly useless.

The corresponding SQL statement (SQL View) looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails;

There is no Join clause between the tables or Join criteria, simply a comma between the tables.

But as I said, there are some rare uses for a Cartesian join.

One almost trivial use is to add a value to each record of a table. For instance, suppose I have a table called User with one record.

User

UserName

Roger

 

If I create a Cartesian join with my Orders table, I'll get:

Cartesian Join 2
OrderID	CustomerID	OrderNumber	UserName
1	1	111	Roger
2	2	222	Roger
3	3	333	Roger
4	1	444	Roger

 

Now, of course there are other ways to do this. However, the Cartesian join has the additional property of being non-updateable. If this is a desirable effect, then a Cartesian join can be useful.

But the main use of a Cartesian join is in creating joins in the Where clause, which I'll talk about in What is a Join: Part 4 (Equi-Joins in the WHERE Clause) .

[top]

What is a JOIN: Part 2 (Inner Join)

Equi-Join (Inner Join)

This is part 2 of a multi-part series on the SQL Join. For an introduction to the whole topic, take a look at:
What Is A Join: Part 1.

This time I'm going to talk about the Equi-Join, also called an Inner Join. An equi-join combines records from two tables which have common values in both tables and displays only those with matching records. Since an equi-join is the most common type of join, it is often simply referred to as a Join.

Consider the following Order and OrderDetail table

Orders
OrderID	OrderNumber
1	111
2	222
3	333
4	444

 

OrderDetails
OrderDetailID	OrderID	Quantity
1	1	1
2	2	2
5	3	3
6	1	2
7	1	1
8	3	2
9	3	1

Joining them on the OrderID field results in:

Single Join Query
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

Notice that although the Orders table has a record for Order Number 444, it does not display in Single Join Query. That's because there are no matching records in OrderDetails. An equi-join shows only those records with matching values in both tables.

The SQL for this query is:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

In the Query Builder, it looks like this:

Notice the 1 and infinity symbol (∞) on the Join line. These indicate that a Relationship exists between these tables and that the OrderID is a Primary Key for the table on the "One-side". This is important, because if you want a query with a join of two tables to be updateable, the field or fields on the "one-side" of the join must either be a Primary Key or a Unique Index. I'll talk more about the updateability of queries in a later post.

Multiple Joins

A query is not limited to a single join. Each join is between just two tables, but the FROM clause of the query can have many joins. For instance, I can add the Products table

Products
ProductID	ProductName	Cost	Price
1	hammer	$1.00	$2.00
2	nail - 10p	$0.01	$0.02
3	saw	$5.00	$10.00
4	wrench	$6.00	$7.00
5	nail - 8p	$0.01	$0.02
6	drill	$20.00	$40.00
7	screw - 1x4	$0.02	$0.04
8	hammer	$4.00	$5.00

to the above query to get the product and price for the order.

Something like this:

MultipleJoin
OrderNumber	OrderID	Quantity	ProductName	Price
111	1	1	hammer	$2.00
222	2	2	nail - 10p	$0.02
333	3	3	saw	$10.00
111	1	2	wrench	$7.00
111	1	1	saw	$10.00
333	3	2	hammer	$2.00
333	3	1	saw	$10.00

In the Query Builder, it looks like this:

The SQL is:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity, Products.ProductName, Products.Price
FROM Products INNER JOIN
(Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID)
ON Products.ProductID = OrderDetails.ProductID;

Next time, I'll discuss the least common type of join, the Cartesian or Cross Join.

 

.