Microsoft

Why isn't my subquery updateable?

The best of intentions often fail when writing a subquery. If you can't update your data via a subquery, there's an alternative.

Not every query is updateable and with good reason. The problem is, we don't always realize we're creating-- nor do we mean to create-- a read-only query. The same problem can occur with a subquery (a query that combines two queries by embedding a SELECT statement within another). Most of the time, you'll use a subquery as a filter in a WHERE clause. Occasionally, the subquery ends up in the SELECT clause. In other words, you want to display the results of the subquery. However, this arrangement produces a read-only query.

Now, the easiest way to make that subquery updateable is to remove the subquery from the SELECT's field list. In most cases, the query will still work fine, if you can live without seeing the results.

If you must display the results of the subquery, I know of only one alternative -- replace the subquery with an equivalent aggregate function, such as DSum(), DLookup(), and so on. The query will be slow as blue blazes, but you'll be able to update it.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments

Editor's Picks