EXISTS reserved word in FROM clause

[Ted Allen]

Hi Stephanie,

Are you writing this query in Access? The syntax looks
different (like SQL Server?). I say this partly because
you are using the % symbol for the wildcard, but
Access/Jet uses the * symbol.

I think you are getting an error referencing EXISTS
because AFAIK that is the only time you can use
subqueries that return multiple fields.

The EXISTS reserved word is used with subqueries
basically as a boolean that will return true if the
subquery returns any records, and false if not.
Therefore it would normally be used to set the condition
for a boolean field (to be honest I never really use
EXISTS).

Unless used with IN (or NOT IN) or EXISTS (or NOT
EXISTS), Access expects only one value for one field to
be returned by a subquery. When using IN (or NOT IN)
Access expects one or more values for a single field.

I get the feeling that the EXISTS statement won't really
be useful in your case, rather I think it is just a case
of needing to adjust your sql syntax to be compatible
with JET, which means breaking up your criteria to
compare directly with the individual fields in your query.

For instance, it looks like the FENAME field condition
could be:

<> " " AND NOT LIKE "*ABANDONED*"

For length it looks like you are trying to restrict the
updates to only those records where the length is equal
to the max length for a particular FENAME. If that is
the case, you need to use a correlated subquery to
restrict the domain of the query such as the following:

(SELECT Max(LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
rail_sw.FENAME)

You could also use Dlookup to achieve this, but I prefer
subqueries and they are often faster.

So, if I can put this together without any typos it would
look something like:

UPDATE rail_sw
SET fetype = 1
WHERE rail_sw.FENAME <> " " AND rail_sw.FENAME NOT
LIKE "*ABANDONED*" AND rail_sw.LENGTH = (SELECT Max
(LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
rail_sw.FENAME)

A couple of other notes, the comparison to the string
with one space, this will only restrict the query from
updating records where the FENAME exactly equals one
space. If you are trying to eliminate blank fields you
may want to add the condition IS NOT NULL, and if you
want to eliminate empty strings, you may want the
condition <>"", or you could combine these by using Nz
(FENAME,"")<>""

HTH, Ted Allen

-----Original Message-----
Hello World,

I am writing an update query that changes a column value

for a group of records based on the maximum value of
another column. The query is:

UPDATE rail_sw
SET fetype = 1
where (FENAME, Length) in
(SELECT FENAME, MAX(LENGTH)
FROM rail_sw
WHERE FENAME <> ' ' AND
FENAME NOT LIKE '%ABANDONED%'
GROUP BY FENAME)

When I try running the query, the following message pops up:
You have written a subquery that can return more than

one field without using the EXISTS reserved word in the
main query's FROM clause. Revise the SELECT statement of
the subquery to request only one field.

When I select Help in the pop-up window, the only

additional information I get is that this is an Error
3306 and to contact Microsoft Product Support Services
for more information.

Can anyone tell me about this EXISTS reserved word and

how I can use it in conjunction with the subquery to run
the update?

 

[Ted Allen]

Hi Stephanie,

Well, we make a good pair because I know Access SQL
syntax fairly well, but am hardly familiar with
SQL/Oracle specifics. I'm an Engineer so I get stuck
using Access on the front end without getting to play
with SQL/Oracle on the back end.

Anyway, from what you described the query syntax that I
posted for you earlier should do what you want, unless I
made a mistake somewhere (there were a few field names
that I didn't explicitly list the table, so I revised the
following). Try pasting the following in a new query's
SQL view (to get to SQL view open a new query in design
view, close the table selection list, then go to view on
the menu and choose sql).

UPDATE rail_sw
SET rail_sw.fetype = 1
WHERE rail_sw.FENAME <> " " AND rail_sw.FENAME NOT
LIKE "*ABANDONED*" AND rail_sw.LENGTH = (SELECT Max
(VT.LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
rail_sw.FENAME);

Run the query and see what happens, I believe it will
give you what you want. To run the query click the
red "!" in design view, or double click from the query
window if the query is not open. Note that in Access
clicking the view button (the one that looks like a
table), will not run the action query, it will only
display the records that will be affected (in this case
it would just display the current values of the fetype
field for the records that would be updated - but you can
get a feel for whether or not it is the right number by
looking at the record count).

If it doesn't work, post back and let me know what
message it gave. As always, it is a good idea to have a
backup of your data before running a new action query.
Although in this case it seems that the worst that could
happen is that it would set some fetype values
incorrectly, so if they are currently all blank it
wouldn't really matter - you could always reset them.

One other thing that you could do is simplify the query
by not worrying about the FENAME criteria. You could
always filter out those names later (or reset them to
0). If you wanted to try that the sql would be:

UPDATE rail_sw
SET rail_sw.fetype = 1
WHERE rail_sw.LENGTH = (SELECT Max
(VT.LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
rail_sw.FENAME);

Post back and let me know how it goes.

-Ted Allen

-----Original Message-----
Thanks for replying so quickly, Ted. Unfortunately the

results of the sample query would not be what I'm looking
for.

You are correct in that I am writing this as SQL

(mostly, because I don't know how to use Access's
querying functions in Design View or Wizard for something
like this.) Although actually, I started out looking for
the right syntax using Oracle. The subquery I wrote is
designed to return name and length values for records
from a table which have the longest segment of a railway
from a set of segments bearing the same name. For
instance if a portion of the table looked something like:

fename length
a 12.3
a 9.5
a 17.6
12.4
b 6.7
b 4.2

The subquery should return the records:
a 17.6
b 6.7

In turn, the main query will flag these segments by

setting fetype = 1. If I just run the subquery's Select,
it is returning the correct records, but I'm not sure how
to set the syntax of the main update query so that it
then locates these records for update.

 

[Ted Allen]

Hi Stephanie,

Glad it worked for you. The design view is pretty easy
once you get the hang of it. You can't use the design
view to do all queries (some can only be done in sql
view), but the one that you ran can be. All you would
have to do is start with a new query, add the table that
you are updating, change it to an update query, add your
update fields and criteria fields to the grid by double
clicking them or dragging them, and add your criteria and
update values. The one thing that is kind of tricky is
adding the subquery as criteria for the length field.
for that field, you would type the subquery in the
criteria row, enclosed in parenthesis. If you switch
your query from sql view to design view you will see what
the final query looks like (if you haven't already).

Once you've done a few of these you will start to get a
feel for it. In the meantime this forum is a great
resource.

Good Luck, Ted Allen

-----Original Message-----
Hello Ted,

The first update command worked like a charm, although I

ended up running it twice (For some reason the first
attempt hung.) I was just trying to combine a couple of
steps by eliminating 2 specific fename entries that were
unnecessary. It looks like I may have to become more
familiar with Access's Design View query usage to
manipulate databases for the GIS application I work with,
although I'll have to figure out whether this type of
query can be created using Design View. Actually, I'm not
all that familiar with the Oracle end either. I had a
short class on it last spring and was working off my text
to try to figure it out. It was supposed to include some
SQL syntax that I hoped would work in SQL View. Obviously
there are some differences.